Month on Month Comparison_

Dear All, Kindly look into the attached PBI, Could you please help me to create a Bar chart for Month on Month comparison like Year on Year comparison as shown in attached PBI. Ex: 2020 June vs...

Need Help

amitchandak

6 years ago

Sansudhi8 , You can try with Date calendar and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

v-zhenbw-msft

Community Support

6 years ago

Hi Sansudhi8 ,

We can use a measure to meet your requirement.

Last Year = 
VAR x =
    CALCULATE (
        SUM ( Sheet1[Charge $] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            Sheet1[Oder Date].[Year]
                = MAX ( Sheet1[Oder Date].[Year] ) - 1
                && Sheet1[Oder Date].[Month] = MAX ( Sheet1[Oder Date].[Month] )
        )
    )
VAR y =
    CALCULATE (
        SUM ( Sheet1[Charge $] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            Sheet1[Oder Date].[Year]
                = MAX ( Sheet1[Oder Date].[Year] ) - 1
        )
    )
VAR z =
    IF ( ISINSCOPE ( Sheet1[Oder Date].[Month] ), x, y )
RETURN
IF ( ISBLANK ( SUM ( Sheet1[Charge $] ) ), BLANK (), z )