Calculate expenses of previous month issues

[cyrilbrd]

Hi, presently using the following:

[COLOR=#000000][FONT=Consolas]PreviousReport = [/FONT][/COLOR][COLOR=#000000][FONT=Consolas]VAR 
    CurrentDate = [Present]
  RETURN
    CALCULATE([Expenses], 
     Raw_data[MONTH] = MONTH(CurrentDate)-2
[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]    )[/FONT][/COLOR]

[Present] is the date of today
[Expenses] is the measure of interest.
This works well from february to December of year n, but starts failing on January of year n+1

Any idea on how to let it compute December if we are on January?

 

[VBA Geek]

Hi Cyril

you may want to first build a date table: http://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

and then check out DAX time intelligence functions, specifically you may need this one: https://docs.microsoft.com/en-us/dax/dateadd-function-dax

 

[cyrilbrd]

Hi, thank you for your feedback.
I already have a date table.
The user requires to see the reports updated dynamically based on the present day, hence a volatile [present]
The reports shown are always 2 months ago versus 1 month ago, so as of January we see December versus November, and the measure is unable to do that.
(was able to trick the measure to show the Dec and Nov of last year but my solution is just a mathematical twist, and may not be the most robust).