CODEWITHSUNDEEP
powerbi
Neha
Neha

Reputation: 3

I want to calculate MTD, YTD, WTD according to fiscal calendar in power bi

The issue is I want to display MTD, WTD, QTD sales according to fiscal calendar for example :- If i selected date 3/9/2017 then MTD should include sales during 2/23/2017 to 3/8/2017 in power bi report.

Is there any formula to get MTD in fiscal calendar?

Upvotes: 0

Views: 2908

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

See this article which walks through the DAX measures you need. Note Iso QTD for example:

Iso QTD :=
IF (
    HASONEVALUE ( Dates[ISO Year] )
        && HASONEVALUE (Dates[ISO Quarter] ),
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            ALL ( Dates ),
            Dates[ISO Year] = VALUES ( Dates[ISO Year] )
                && Dates[ISO Quarter] = VALUES ( Dates[ISO Quarter] )
                && Dates[Date] <= MAX ( Dates[Date] )
        )
    ),
    BLANK ()
)

Upvotes: 2

Related Questions