Building Measures with Month Filter

[jonvesguerra]

Hello all, 

I want to build a visual that gives me two columns: This Month's Revenue and Last Month's Revenue. I also want to be able to filter this visual by month so that the data for the selected month from the filter shows under "This Month's Revenue" and the month prior shows for "Last Month's Revenue". For example, today is may so by default May data would show under "This Month's Revenue" and April data would show under "Last Month's Revenue". If I were to select February in the filter, February data would should under "This Month's Revenue" and January data would show under "Last Month's Revenue".

I built my measures, "This Month's Revenue" and "Last Month's Revenue" with the dax formulas listed below. My "This Month's Revenue" column works great, by default it gives me the data for the current month as well as the correct data for the month I choose with the filter. However, I can't figure out how to create the "Last Month's Revenue" work with a filter. By default (no filter) it correctly gives me the previous month data in relation to today's date, but as you can see it shows as blank when I do apply a filter (It should give me data for January since February was selected in the current month filter).

Is something like this possible? Please let me know if I need to clarify anything.

Current Month Revenue =
CALCULATE(

    SUM('Balance Table'[Cost]),

    'Balance Table'[Balance Date]=MAX('Balance Table'[Balance Date])

)

Previous Month Revenue =

VAR CurrentMonth = MONTH(MAX('Balance Table'[Balance Date]))

VAR CurrentYear = YEAR(MAX('Balance Table'[Balance Date]))

VAR PreviousMonthStartDate = DATE(CurrentYear, CurrentMonth - 1, 1)

VAR PreviousMonthEndDate = EOMONTH(PreviousMonthStartDate, 0)

RETURN

CALCULATE(

    SUM('Balance Table'[Cost]),

    'Balance Table'[Balance Date] >= PreviousMonthStartDate,

    'Balance Table'[Balance Date] <= PreviousMonthEndDate

)

[amitchandak]

@jonvesguerra , Join date of your table with date of a date table and try measures like

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))
last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*month(Today()),MONTH)))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA