CODEWITHSUNDEEP
powerbiexcel-2010dax
Sajeev A
Sajeev A

Reputation: 15

Power BI DAX to get previous month value from custom date

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

Upvotes: 1

Views: 3959

Answers (1)

Seymour
Seymour

Reputation: 3274

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.

Upvotes: 1

Related Questions