Forum Discussion

sraj's avatar
sraj
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Year & month comparision

Hi everyone, Is there anyway to show the difference between years?  Both Years/Months are in the same table, can someone please advise?    
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi sraj,

     

    You can try measure as:

    Measure = 
IF(
    HASONEVALUE('Table'[Date]),
    MAX('Table'[Sales]),
    CALCULATE(
        MAX('Table'[Sales]),
        FILTER(
            'Table',
            YEAR('Table'[Date])=2021)
    )-
    CALCULATE(
        MAX('Table'[Sales]),
        FILTER(
            'Table',
            YEAR('Table'[Date])=2020)
    )
)

    Here is the output:

    The pbix is attached.

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

sraj , the display you want needs month and year separately so that it can combine.

 

How do you want to diff between year and month?

 

You need to have a year month table, I sometimes create date from the year month and use date table. But you need separate table 

 

Add month rank as column in table on year month

 

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)

 

 

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Last Year same month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month]=max('Period'[Month]) && 'Date'[Year]=max('Date'[Year])-1))

 

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