Forum Discussion

Balhemkan's avatar
Balhemkan
Icon for Helper III rankHelper III
5 years ago
Solved

Current year and previous year multi selection

      Hello Team,   I have Data table(image).   I have created two measure for Current Year and Previous Year which is working fine.   I am using separate year slicer and separat...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Balhemkan

     

    Check if this is what you want:

    You can create a dimtable and apply to Year slicer.

    Try measures as:

    Current_Year = 
CALCULATE(
    SUM('Table'[salesamount]),
    FILTER(
    ('Table'),
    'Table'[Year]=MAX('Year Slicer'[Year])
))
    Previous_Year = 
CALCULATE(
    SUM('Table'[salesamount]),
    FILTER(
    'Table',
    'Table'[Year]=MAX('Year Slicer'[Year])-1
))

    The pbix is attached.

     

    Best Regards,
    Link

     

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

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

Balhemkan , use time intellignece and date table

 

example measure

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

 

or

 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

or

 


YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

 

//month is number

 

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

 

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.