Calculation Groups for Selected date, Previous day of seected date

[01UmaManoharan]

Hi All,

I'm trying to create a calculation group for multiple measures with following

1. Selected date & Previous date of selected date

2. Selected date's week & Previous week of selected date's week

3. Selected date's Month & previous Month of selected date's Month

Ex: if I pick Jan 22/2024 in date slicer, calc group should give me the values of  Jan22, Jan 21, Week 4, week3, Jan 2024 and dec 2023 for total sales

Thank you

[amitchandak · Accepted solution]

@01UmaManoharan , You can have following calculation item with help from date table

MTD Sales = CALCULATE(Selectedmeasure(),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(Selectedmeasure(),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(Selectedmeasure(),previousmonth('Date'[Date]))

this day =Selectedmeasure()

month behind = CALCULATE(Selectedmeasure(),dateadd('Date'[Date], -1, month)

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These items in calculationgroup can help
This Week = CALCULATE(Selectedmeasure(), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(Selectedmeasure(), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[01UmaManoharan · Accepted solution]

Thak you Amit. I have got the solutions. This trick works!!

[amitchandak · Accepted solution]

@01UmaManoharan , You can have following calculation item with help from date table

MTD Sales = CALCULATE(Selectedmeasure(),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(Selectedmeasure(),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(Selectedmeasure(),previousmonth('Date'[Date]))

this day =Selectedmeasure()

month behind = CALCULATE(Selectedmeasure(),dateadd('Date'[Date], -1, month)

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These items in calculationgroup can help
This Week = CALCULATE(Selectedmeasure(), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(Selectedmeasure(), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s