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 separate month slicer

Year slicer: 2019,2020,2021

month Slicer: Jan,Feb,Mar,Apr.....Dec.

 

Current Year =

Var selectedyear = SELECTEDVALUE('Date table'[Year])

var Result = CALCULATE(SUM(Data[salesamount]),FILTER(Data,AND(Data[year]=selectedyear,data[MonthNumber]<=SELECTEDVALUE('Date table'[Monthnumber]))))

return result

 

PreviousYear =

Var selectedyear = SELECTEDVALUE('Date table'[Year])

var minyear = MIN(Data[year])

Var Previousyear = SELECTEDVALUE('Date table'[Year])-1

var result = IF(LEN(selectedyear)= 0,CALCULATE(SUM(Data[Salesamount]),FILTER(Data,(Data[year]= minyear))),CALCULATE(SUM(Data[Salesamount]),FILTER(Data,AND(Data[year]=Previousyear ,Data[MonthNumber]<=SELECTEDVALUE('Date table'[MonthNumber])))))

return result

 

I also used disconnected Date table for this.

 

If I am using above measure example if I select april 2021 in month slicer data will come for Jan,Feb,Mar,Apr for current year and Jan,feb,mar,Apr data for Previous year (2020)

 

but if I use multi selection it is not working it is coming as blank.

 

Users want to use multi selection.

example: if user select Jan and Apr in monthslicer for 2021. They want to see data for only Jan and april 2021.

Same way if we are using above previousyear measure they want to use multi selection.

 

Currently multi selection option is not working if i use above measure.

 

Please help me to resolve this issue.

  • 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.

3 Replies

  • 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.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support

    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.