Power BI DAX to get previous month value from custom date

Question

I have a data which is having FiscalDate information (Fiscalyear starts from July). Currently there is a column which shows Revenue, but the Revenue is Fiscal Year to date. The requirement is to get Current month and previous month revenue. Since its a custom date, I cannot use the standard date function to get previous month value. Could anyone please suggest Dax which can provide me the previous month Revenue. It should work even if extra filters are added to the report. Given data set enter image description here

expected output

enter image description here

PREVIOUSMONTH is what you want. Previousmonth of calendar year or previous month of fiscal year is the same thing. — mxix
Previousmonth will not work, because the first month of fiscalyear is July ,so if I apply previous month I will get June, which is wrong result — Sajeev A

Seymour Seymour · Accepted Answer · 2020-04-18T15:25:25

The problem is not very clear. From my understanding: you need a previous month formula that for the first month of the fiscal year (July) returns 0.

Solution:

Previous Month KPI:=
VAR _FiscalYearFirstMonth = 7
        IF(
           'Calendar'[Month code] = _FiscalYearFirstMonth, 0,
           CALCULATE([MEASURE], PREVIOUSMONTH('Calendar'[Date])
        )

If the current month is the beginning of fiscal year (July) then return 0 else compute the previous month.

Power BI DAX to get previous month value from custom date

1 Answers