Static Week Time Intelligence

[TrentS]

Good day all,

I have a report that will only be updated on Mondays. Currently, I have a column created with the following:

Last Update = IF(
'Table'[Status_Name] = "OPEN" || 'Table'[Status_Name] = "Pending",

IF(
ISBLANK(Table[Last_Update_Date]), DATEDIFF(Table[Open_Date_Non_Blank], TODAY(), DAY),

DATEDIFF(Table[Last_Update_Date], TODAY(), DAY)
))

I also have a custom Date table with a "week_ending" column (Sundays) available. The report will only be refreshed on Mondays but could be accessed for information at anytime. This column is used in other calculations like determining average of how many days since the case was last updated, % of contacts /week, etc.

For example, a measure using the formula:

Measure = CALCULATE(COUNTROWS(Table),FILTER(Table, Table[Last Update]<=7),Table[Status_Name] = "Open")

This gives me a count of the open cases that were updated in the last 7 days.

 

While the formula and subsequent calculations work, the TODAY function modifies the results when looked at after Monday. 

What do I need to replace that TODAY function with so that it only displays the DATEDIFF based on the report refresh date?

Essentially, I need it static not dynamic based on the day the report is viewed. What it is on Monday is what it should show until the refresh the following Monday.

Aside: If anyone also has a suggestion on how to track or calculate a trend of the result from the final formula, please suggest it! Apart from manually recording it and loading it to an Excel file for charting, I am at a loss...

Thanks!

Trent

[TomMartens]

Hey,

maybe you find this useful: https://community.powerbi.com/t5/Desktop/Week-commencing-in-DAX/m-p/241304

I can imagine that you create a column that will always have the date of the last Monday, regardless when the report will be used.

Regards,

Tom

[TrentS]

Thanks Tom.

I have a EoW and SoW in both my Date table and tried it inside the source table itself, just to eliminate any relational issues. I replaced the Today call with the End of WEEK fields and don't get the expected results.

Suggestions on how I would call those fields within a column such as in my formula above?

[amitchandak · Accepted solution]

In case, you want to get last Monday every time.

1. Try taking the max of Last update date into a Var. Ideally, it should give Max date of data

2. Take Weekend in a Var use it.

Calculate(max(weekend_date),ddate<today)

3. Monday has WeekDay 2. If Today is more than that or 1 then adjust 

if(WEEKDAY(Today())<>2, Today()-WEEKDAY(Today())+2,WEEKDAY(Today())

Need to handle when it is 1