Compute MTD of last year base on selected month

[Anonymous]

Hi All,

I have a Year and Month Filter on my report. I want to display the MTD comparison for the last 2 years. My issue however is that when I select Sep 2019 on the filter, the bar chart shows MTD for Dec 2018 and MTD for Sep 2019. What I want is that when I select Sep 2019, I should be able to see MTD for Sep 2019 and MTD for Sep 2018. Below is my measure. Please help!!

compute_distribution_indicators = CALCULATE(
IF (
    SELECTEDVALUE ( indicators[name] ) = "END",
    ROUND(TOTALMTD([total_end], interruption[CODE_MOIS]), 0),
    IF (
        SELECTEDVALUE ( indicators[name] ) = "SAIDI*100",
        ROUND(TOTALMTD([total_saidi], interruption[CODE_MOIS])*100, 0),
        IF (
            SELECTEDVALUE ( indicators[name] ) = "SAIFI*100",
            ROUND(TOTALMTD([total_saifi], interruption[CODE_MOIS])*100, 0)
        )
    )
), DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]),-2, YEAR))

 

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

1. Create a table without any relationships among other tables.

Year = VALUES(DimDate[Year])

2. Create a CheckMeasure. Then drag the CheckMeasure to the Visual level filters of your Column chart and filter the measure to 1 only.

CheckMeasure =
IF (
    MAX ( interruption[YEAR] )
        > ( MAX ( 'Year'[Year] ) - 2 )
        && MAX ( interruption[YEAR] ) <= MAX ( 'Year'[Year] ),
    1,
    0
)

3. Create the year slicer using your Year table, and the month slicer using your DimDate table.

4. Change your measure "compute_distribution_indicators" like so:

compute_distribution_indicators = 
IF (
    SELECTEDVALUE ( indicators[name] ) = "END",
    ROUND(TOTALMTD([total_end], interruption[CODE_MOIS]), 0),
    IF (
        SELECTEDVALUE ( indicators[name] ) = "SAIDI*100",
        ROUND(TOTALMTD([total_saidi], interruption[CODE_MOIS])*100, 0),
        IF (
            SELECTEDVALUE ( indicators[name] ) = "SAIFI*100",
            ROUND(TOTALMTD([total_saifi], interruption[CODE_MOIS])*100, 0)
        )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey 

It works like charm. Thanks alot

Best Regards,

Divo

[Icey]

Hi @Anonymous ,

If you don't mind, please share me a dummy PBIX file without real data and sensitive information.

Best Regards,

Icey

[Anonymous]

Hi @Icey 

Below is a link to the pbix with dummy data.  Notice in the image below that when I select Sep 2019, the END value of 4 is of Dec 2018. I will have love to see the MTD of Sep 2018 which is 3

https://www.dropbox.com/s/mdkffdx648rq8rl/MTD%20Versus%20MTD%20Previous%20Years.pbix?dl=0 

[amitchandak]

Not sure I got it completely. But try these

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 MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date Filer])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date Filer],-12,MONTH))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601