Current year and previous year multi selection

[Balhemkan]

 

 

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.

[v-xulin-mstf · Accepted solution]

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.

[Balhemkan]

I Tried it is not working for multi selection

[amitchandak]

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