how to select last day (max day) of each month using dax

powerbi dax
D. Watson picture D. Watson · Aug 13, 2018 · Viewed 7.5k times · Source

I have the following table:

enter image description here

I need to make a measure to return the values of "last day", but I cant use EOMONTH, because I have the current month, and the current month doesnt end yet, so, the last day of current month is today.

Answer

Alexis Olson picture Alexis Olson · Aug 13, 2018

You can use EOMONTH with a bit of extra logic:

LastDay =
    VAR CurrDate = MAX(Table1[Date])
    RETURN CALCULATE(MAX(Table1[Date]),
               FILTER(ALL(Table1),
                   Table1[Date] >  EOMONTH(CurrDate, -1) &&
                   Table1[Date] <= EOMONTH(CurrDate, 0)))

This takes the max of the dates you have that occur between the end of the previous month and the end of the current month.

Once you have this measure, you can use it to calculate the sum of Value:

Last Date Sum = CALCULATE(SUM(Table01[Value]),
                    FILTER(Table01, Table01[Date] = [LastDay]))

Related questions

how to convert a Integer to Text value in Power BI

I am creating a calculated column in existing power BI report. the calculated column concatenates integer & text columns. I tried below query which give syntax error to me, = Number.ToText(table1.[RegionID]) & " " & table1.[RegionName] I tried some …

Read More
How to format new measure (not column) in power BI as percentage?

When I create a new measure with formula, i.e. sum(col1)/max(col2), it automatically converts the result into #.#% format. Strangely, it doesn't happen all the time; sometimes it just gives result #.## format. Any idea of how to format …

Read More
How to get month name from month number in Power BI?

I have Year number and Month Number in my data. How using DAX can I get the month name out of month number? In SSRS its very easy. But how to achieve that using DAX?

Read More