Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Comparing Previous Year vs Current Year-Month

Imagine we have data like so,

 

Range: Jan 1st 2013 - March 1st 2014

 

Date: | Units
1-Jan-2013 | 100

2-Jan-2013 | 100

.

1-Mar-2013 | 100

.

1-Feb-2014 | 100

2-Feb-2014 | 100

.

28-Feb-2014 | 100

 

I would like to build a bar+line graph which displays the SUM of each month. 

Y-Axis, Units
X-Axis, Year-Month
Line, (Previous Year SUM / Month SUM)

(Example: From Febuary 1st 2013 - Febuary 1st 2014 we have a PreviousYearSUM of,  36,500 Units. Divided by, 2800 in the 2014-Feb month. This results in, 13.03%.)

The line graph will be responsible for holding the "% of the previous year by year-month." 
I am unable to determine how to build the correct measure to display the "% of previous year by year-month". 

If I'm still confusing you please ask and I'll try to elaborate further.

 

Connor

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , With help from date table

     

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

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

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

     

    diff = [this month] - [last year MTD Sales]

    diff% = divide([diff],[last year MTD Sales])

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

    Assume you have a Date table in your model, you can create a measure like below to achieve that. Download this PBIX file for more details.

    Previous Year SUM / Month SUM = DIVIDE(CALCULATE(SUM(TestData[Units]),DATESINPERIOD('Date'[Date],MIN('Date'[Date])-1,-1,YEAR)), SUM(TestData[Units]))

     

    BTW, to get the "% of previous year by year-month", it seems the numerator and denominator should exchange their places (e.g. 2800 / 36500).

     

    Kindly let me know if this helps.
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.