How to get measure values correctly as per two slicer selection?

[Anonymous]

Hi Team,

I have two slicers : 'Select Date Type' and 'Select Period Type'. I am able to get Dates as per the selection done in both the slicers. 

Requirement: Based on both slicer selection sum of discount needs to be calculated. 

Can you please help in getting correct measure values as per the selection done in both the slicers.


Thanks in advance,
Sukriti

[v-yalanwu-msft]

Hi, @Anonymous ;

Is this text type? if so, you could change to whole number.

Best Regards,
Community Support Team_ Yalan Wu

[Anonymous]

Hi Yalan Wu,

Data type is whole number. 
The pbix file shared is also throwing error when we select 5,6,7,1.

Thanks & Regards,

Sukriti

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create another slicer about the start of week. then change the 2 to selectvalue by week slicer.

selectvalue = 
var _selvalue=MAX('weekstart'[value])
return 
SWITCH(MAX('slicertable'[Slicer2]),
       "Year",IF(MAX([Date])=STARTOFYEAR('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),YEAR([Date])=YEAR(MAX([Date]))),[DiscountMeasure])),
       "Quarter",IF(MAX([Date])=STARTOFQUARTER('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),YEAR([Date])=YEAR(MAX([Date]))&&QUARTER([Date])=QUARTER(MAX([Date]))),[DiscountMeasure])),
       "Month",IF(MAX([Date])=STARTOFMONTH('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),EOMONTH([Date],0)=EOMONTH(MAX('Orders'[Date]),0)),[DiscountMeasure])),
       "Week",IF(WEEKDAY(MAX([Date]),_selvalue-1)=1,
                SUMX(FILTER(ALLSELECTED('Orders'),WEEKNUM([Date],_selvalue+10)=WEEKNUM(MAX([Date]),_selvalue+10)),[DiscountMeasure])),
       "Daily",[DiscountMeasure])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yalan Wu,

Thanks for the quick response.

We have tried with the given solution. However, it is not giving the expected results.

 a) On selecting Monday, Tuesday, Wednesday the chart is getting populated. But for rest of the values Thursday to Sunday, the chart is throwing error.

b) Also, we have observed that when we pass Monday in "day" slicer through dynamic calculation "selectvalue_week" is not matching with the default hard coded "selectvalue" WEEKNUM([Date],2) calculation.

Below is the screenshot for your reference:

Below is the screenshot of slicer which we have created:

Thanks,

Sukriti

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a table about {"Year","Quarter",...} as a slicer2, then create a measure like below:

1.enter table.

2.create a measure.

selectvalue = 
SWITCH(MAX('slicertable'[Slicer2]),
       "Year",IF(MAX([Date])=STARTOFYEAR('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),YEAR([Date])=YEAR(MAX([Date]))),[DiscountMeasure])),
       "Quarter",IF(MAX([Date])=STARTOFQUARTER('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),YEAR([Date])=YEAR(MAX([Date]))&&QUARTER([Date])=QUARTER(MAX([Date]))),[DiscountMeasure])),
       "Month",IF(MAX([Date])=STARTOFMONTH('Orders'[Date]),
                SUMX(FILTER(ALLSELECTED('Orders'),EOMONTH([Date],0)=EOMONTH(MAX('Orders'[Date]),0)),[DiscountMeasure])),
       "Week",IF(WEEKDAY(MAX([Date]),2)=1,
                SUMX(FILTER(ALLSELECTED('Orders'),WEEKNUM([Date],2)=WEEKNUM(MAX([Date]),2)),[DiscountMeasure])),
       "Daily",[DiscountMeasure])

3.change the filter about the visual.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks v-yalanwu-msft for providing the solution. It worked for us.

Also, one quick question for the below calculation suggested by you:

 "Week",IF(WEEKDAY(MAX([Date]),2)=1,
                SUMX(FILTER(ALLSELECTED('Orders'),WEEKNUM([Date],2)=WEEKNUM(MAX([Date]),2)),[DiscountMeasure])),

In above example the start of week is 'Monday' we need to change start of week through slicer.

How can we change start of week based on dynamic slicer selection?
'Sunday', 'Monday', 'Tuesday' and so on....

Many thanks,
Sukriti

[kauravneet]

Hi Amit,

Thank you for the quick response.

We need period calculations - year/quarter/month/week based upon selected slicer (either order date or ship date), not today. 

Case I. Selected ship date in first slicer and selected year in second slicer so based upon these two selections, measure(discount) should be aggregated by year.

Case II. Selected order date in first slicer and selected month in second slicer so based upon these two selections, measure(discount) should be aggregated by month.

Note: discount data is available at day level(order date/ship date).

Thanks in advance,

Avneet

[amitchandak]

@Anonymous , first create measures like

then refer these time intelligence function , use field from date table in slicer or visual

Use these measures in time intellignece

Ship Value =calculate(sum(Table[discount]), USERELATIONSHIP(Date[date], Table[Ship date]))

Order value=calculate(sum(Table[discount]), USERELATIONSHIP(Date[date], Table[Order date]))

example

Ship Value mtd = calculate([Ship Value], datesmtd('Date'[Date]))

Ship Value qtd = calculate([Ship Value], datesqtd('Date'[Date]))

Ship Value ytd = calculate([Ship Value], datesytd('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
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
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[Anonymous]

Hi Amit,

Thanks for your response.
We have one main table named as 'Orders' which consists of two dates: 'Order Date' and 'Ship Date'. Now inorder to fetch sum of discount by Order/Ship Date, we have created a manual Calender table which is joined to 'Orders' table with help of below two DAX functions.

Ship Value = calculate(sum(Orders[Discount]), USERELATIONSHIP(Calender[Date], Orders[Ship date]))

Order Value = calculate(sum(Orders[Discount]), USERELATIONSHIP(Calender[Date], Orders[Order date]))

Below is the relationship diagram for your reference:

With help of Measure slicer we are switching between OrderValue/ShipValue. 

Based upon slicer selection we are getting correct values for year/quarter/month/day by drill mode functionality. Since week is not available in drill mode. 

So, we were thinking to create another slicer with help of which we can switch between periods. But we dont see the correct measure value at year/quarter/month/week. However, it works perfectly at day level.

In above example, at month we should get 1561.09 instead of 43.01
Can you please help us to resolve the issue?

Thanks in advance,
Sukriti

[amitchandak]

@Anonymous , first you need a common date table joined with these dates and then you can have measures like

Ship Value  =calculate(sum(Table[value]), USERELATIONSHIP(Date[date], Table[Ship date]))

Order value=calculate(sum(Table[value]), USERELATIONSHIP(Date[date], Table[Order date]))

If year/month/qtr week are based todat not date you need measures liek

This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )

This month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0) //today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

Week =
var _max1 = maxx(allselected('Date'), 'Date'[Date]) // or _max = today()
var _stweek = _max1 +-1*WEEKDAY(_max1,2)+1
var _edweek= _max1+ 7-1*WEEKDAY(_max1,2)
return
calculate(sum(Table[Value]), filter(date, Date[Date] >=_stweek && Date[Date] <=_edweek))
// or use all date
//calculate(sum(Table[Value]), filter(all(date), Date[Date] >=_stweek && Date[Date] <=_edweek))

example

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

You need switch true or like measure slicer or you need calculation group

Switch( True() ,

selectedvalue(Date[Type]) "Ship Date" ,Switch(selectedvalue([Period]) ,

"Year", [ship Year],

"Month", [ship Month]

// add others ) ,

,Switch(selectedvalue([Period]) ,

"Year", [order Year],

"Month", [order  Month]

// add others )

)

refer

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/