Month on Month Comparison_

[Sansudhi8]

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 2019 June. 

PBI

Thank you,

Sansudhi. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sansudhi8]

Hi @Ashish_Mathur ,

This is great . Thank you very much. 

Sansudhi. 

[Ashish_Mathur]

You are welcome.

[v-zhenbw-msft]

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 )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[Sansudhi8]

Hi @v-zhenbw-msft ,

Yes , This is what I need. Thank you very much. 

Sansudhi. 

[amitchandak · Accepted solution]

@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-Y...


Appreciate your Kudos.

[Sansudhi8]

Hi @amitchandak ,

This is a great document. Thank you very much. 

Sansudhi.