Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tim Dwyer]

I currently have a report that pulls data from different tables with different date tables. These are for open, won, and quoted Opportunities to show both the count and amount for the past 7 days. Depending upon the business unit/time zone they are scheduled to go out almost every day. I have relative date filters on the visuals for their corresponding date table.
Ask from management, show red/yellow/green arrows comparing the previous seven days count or sum in that visual relative to the date. I have done this with conditional formatting.
I am not sure how to write the dax to show previous 7 days over seven days before that to know whether it is up or down or even. I can’t define the day of the week since there are filters on the visuals keep it to the day and I wish I could sync that with dax. Any idea’s?
Thank you,
Tim

——————————
Tim Dwyer
System Admin
Houston TX
2817730083
——————————

[David Gerard]

Hi ,

It might be very difficult for one to create a good image of your model.
It would be very helpful if you could share a sample of your model PBIX or at minimum some screenshots of the tables, the model and how your data would be represented with required filters.

Regards?

——————————
David Gerard
Business Intelligence Consultant
Kampala
——————————
——————————————-

[Tim Dwyer]

Hi David,

Yes, Attached is the Page level filter. Also, the 4 visuals on the page that I am trying to compare to the previous seven days.

——————————
Tim Dwyer
System Admin
HG
Houston TX
2817730083
——————————
——————————————-

[Greg Philps]

Hi Tim. I agree that a sample file would be a great help, but I imagine something using AVERAGEX and DATESIINPERIOD might work. Here’s a quick-and-dirty sample. Hope it helps. GregHi Tim. I agree that a sample file would be a great help, but I imagine something using AVERAGEX and DATESIINPERIOD might work. Here’s a quick-and-dirty sample. Hope it helps. Greg

1-7 Days Ago Rolling Average Sales = AVERAGEX(    DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date]), -7, DAY ),    [Total Sales]    )
8-14 Days Ago Rolling Average Sales = AVERAGEX(    DATESINPERIOD( Dates[Date], DATEADD( LASTDATE( Dates[Date]), -8, DAY ), -7, DAY ),    [Total Sales]    )
Up or Down = SWITCH( TRUE(),    [1-7 Days Ago Rolling Average Sales] > [8-14 Days Ago Rolling Average Sales], “UP”,    [1-7 Days Ago Rolling Average Sales] < [8-14 Days Ago Rolling Average Sales], “DOWN”,    “SAME”    )

——————————
Greg Philps
Power BI Consultant
Ottawa ON Canada
——————————
——————————————-