Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mahmed1
Helper IV
Helper IV

Sum Last X Day based on selection

Hi All

 

How do get a measure that goes back x amount of days and adds up

 

so say i selected yesterdays date (06.02) and i want to be able to get the sum of categories for last 4 Mondays

 

ie

 

sum of dates (9th, 16th, 23 and 30th of Jan) and then divide by the overall totall for those dates so that way i can get the % of total for last 4 mondays for those categories

 

so i want to essentially go back last 4 or whatever i select days based on selection so i can compare the same DAY trend over a given period

 

I do have a week day column in my calender if that helps

 

thank you

 

i dont have access to the file at present

 

 

3 REPLIES 3
Mahmed1
Helper IV
Helper IV

Hi

 

I managed to sort it using the belowvode but now i need to amend this code to get the average

 

would his be correct

 

Handled Calls Prev 4 Equivalent Days =

 

 VAR Date_Selected = SELECTEDVALUE(Calender[Date])

 VAR Weekday_Date_Selected = WEEKDAY(Date_Selected,3)

 VAR Equivalent_Days_To_Go_Back = 28 // Go back previous 4 weeks

 

RETURN

  CALCULATE([Handled]Calender[Date]>=Calender[Date]- Equivalent_Days_To_Go_Back && Calender[Date]<Date_Selected && Calender[DayOfWeek]Weekday_Date_Selected)

 

i just need the above changed to get the average handled too instead of calculate/sumx

Mahmed1
Helper IV
Helper IV

Hi Guys,


Any luck withe where im going wrong with the dax code?

 

Thank you

 

really appreciate it

Mahmed1
Helper IV
Helper IV

I tried to have a go but getting blank results...

 

Handled Calls Prev 4 Equivalent Days =

 

 VAR Date_Selected = SELECTEDVALUE(Calender[Date])

 VAR Weekday_Date_Selected = WEEKDAY(Date_Selected,3)

 VAR Equivalent_Days_To_Go_Back = 28 // Go back previous 4 weeks

 

RETURN

  CALCULATE([Handled],FILTER(Calender,Calender[Date]>=DATEADD(Calender[Date],Equivalent_Days_To_Go_Back,DAY) && Calender[Date]<Date_Selected && Calender[DayOfWeek]Weekday_Date_Selected)

)

 

 

 

All Handled Calls Prev 4 Equivalent Days =

 

 VAR Date_Selected = SELECTEDVALUE(Calender[Date])

 VAR Weekday_Date_Selected = WEEKDAY(Date_Selected,3)

 VAR Equivalent_Days_To_Go_Back = 28 // Go back previous 4 weeks

 

RETURN

  CALCULATE([Handled],FILTER(ALL(Calender),Calender[Date]>=DATEADD(Calender[Date],Equivalent_Days_To_Go_Back,DAY) && Calender[Date]<Date_Selected && Calender[DayOfWeek]Weekday_Date_Selected)

)

 

 

 

Handled Calls Prev 4 Equivalent Days Percentage = DIVIDE([Handled Calls Prev 4 Equivalent Days],[All Handled Calls Prev 4 Equivalent Days])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors