DAX Start of Week Measure Going Blank on Service

Whitewater100 · July 19, 2022, 8:12pm

Hello:
I was able to get some help on this question a couple-few weeks ago. I have a need to show Start of Week showing as a Monday (in a card visual). If Monday is a holiday, then Tuesday would appear.
I have set the date slicer as relative (This Day) and have a measure that works. I also want to calculate the previous weeks start date(with the same criteria). These are the two measures that work fine on Desktop but go blank on service.

Report Start Date** =
var _selectedYear = SELECTEDVALUE(Dates1[year])
var _selectedWeek = SELECTEDVALUE(Dates1[Week No.])
var _startOfTheWeek =
CALCULATE(
FIRSTDATE(Dates1[Date]),
FILTER(ALL(Dates1),
Dates1[Week No.] = _selectedWeek && Dates1[year] = _selectedYear && Dates1[Flag] = “WeekStart”)
)
return
_startOfTheWeek
**PW Report Start Date =
var _selectedYear = SELECTEDVALUE(Dates1[year])
var _selectedWeek = SELECTEDVALUE(Dates1[Week No.])-1
var _startOfTheWeek =
CALCULATE(
FIRSTDATE(Dates1[Date]),
FILTER(ALL(Dates1),
Dates1[Week No.] = _selectedWeek && Dates1[year] = _selectedYear && Dates1[Flag] = “WeekStart”)
)
return
_startOfTheWeek

Should this be done instead in PQ or is there a trick with my date slicer to make the dates persist on Service?

Thank you very much!
File Attached.
Start of Week Monday or Tuesday(if holiday)

*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

Your current work-in-progress PBIX file - VERY IMPORTANT
A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

Harsh · July 20, 2022, 5:51am

Hello @Whitewater100,

Thank You for posting your query onto the Forum.

Well, we are also facing quite a weird or strange problem. That is, when I downloaded the file and published it straight away onto the Web Service, it gave me the results as blanks but when I re-published it again, it started to showcase the results correctly. Below are the screenshots provided for the reference -

As you can see in the above screenshot, it gives the results as blanks. This actually happens when it’s published for the first time. But now, when it’s re-published again, it showcases the results. See below screenshot -

I crossed check the exact the same issue with my expert colleagues as well who are located in different time zones and they also witnessed the same thing. So can you also try the same on your side as well by re-publishing it?

Also, it was learnt based on the note written into the text box that you’re using relative date slicer just to showcase the results of the card visuals. If you want to showcase the same results where date referenced is always equal to today then you can simply make that adjustment inside the measure itself rather than using the space of the report by adding a date slicer to showcase the results. Below are the measures provided for the reference -

Report Start Date - Harsh - 1 =
VAR _Current_Year = 
YEAR( TODAY() )

VAR _Current_Week = 
WEEKNUM( TODAY() , 1 )

VAR _Start_Of_The_Week =
CALCULATE( FIRSTDATE( Dates1[Date] ) ,
    FILTER( ALL( Dates1 ) ,
    Dates1[Week No.] = _Current_Week &&
    Dates1[year] = _Current_Year &&
    Dates1[Flag] = "WeekStart" ) )

RETURN
_Start_Of_The_Week




Previous Report Start Date - Harsh - 1 =
VAR _Current_Year =
YEAR( TODAY() )

VAR _Current_Week =
WEEKNUM( TODAY() , 1 ) - 1

VAR _Previous_Start_Of_The_Week =
CALCULATE( FIRSTDATE( Dates1[Date] ) ,
    FILTER( ALL( Dates1 ) ,
    Dates1[Week No.] = _Current_Week &&
    Dates1[year] = _Current_Year &&
    Dates1[Flag] = "WeekStart" ) )

RETURN
_Previous_Start_Of_The_Week

Or still if you want to showcase the results as per the date slicer then you can use the below provided measures. The reason being is, when you change the date in your slicer the current measure’s which are there in your report they yield the results as blanks. So rather you can replace them with the measures provided below -

Report Start Date - Harsh - 2 =
VAR _Current_Year =
YEAR( MIN( Dates1[Date] ) )

VAR _Current_Week =
WEEKNUM( MIN( Dates1[Date] ), 1 )

VAR _Start_Of_The_Week =
CALCULATE( FIRSTDATE( Dates1[Date] ) ,
    FILTER( ALL( Dates1 ) ,
    Dates1[Week No.] = _Current_Week &&
    Dates1[year] = _Current_Year &&
    Dates1[Flag] = "WeekStart" ) )

RETURN
_Start_Of_The_Week




Previous Report Start Date - Harsh - 2 =
VAR _Current_Year =
YEAR( MIN( Dates1[Date] ) )

VAR _Current_Week =
WEEKNUM( MIN( Dates1[Date] ), 1 ) - 1

VAR _Previous_Start_Of_The_Week =
CALCULATE( FIRSTDATE( Dates1[Date] ) ,
    FILTER( ALL( Dates1 ) ,
    Dates1[Week No.] = _Current_Week &&
    Dates1[year] = _Current_Year &&
    Dates1[Flag] = "WeekStart" ) )

RETURN
_Previous_Start_Of_The_Week

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and helps you in solving your problem.

Thanks and Warm Regards,
Harsh

holidayexample - Harsh.pbix (1.1 MB)

Whitewater100 · July 20, 2022, 1:10pm

Harsh:

VAR _Current_Year =
YEAR( MIN( Dates1[Date] ) )

VAR _Current_Week =
WEEKNUM( MIN( Dates1[Date] ), 1 ) - 1

VAR _Previous_Start_Of_The_Week =
CALCULATE( FIRSTDATE( Dates1[Date] ) ,
    FILTER( ALL( Dates1 ) ,
    Dates1[Week No.] = _Current_Week &&
    Dates1[year] = _Current_Year &&
    Dates1[Flag] = "WeekStart" ) )

RETURN
_Previous_Start_Of_The_Week

Dear Harsh:

Thanks so much for your great input and your very complete answer! I will implement this straight away. I really appreciate you showing the different alternatives and the explanation about the pitfalls of using relative date slicer. Have a great day!

BrianJ · July 20, 2022, 1:48pm

@Harsh - amazing response! This was like reading a well-done blog entry.

Thank you for going the extra mile in your responses to provide these detailed explanations in addition to excellent DAX solutions.

Brian

Harsh · July 21, 2022, 7:47am

Hello @Whitewater100,

You’re Welcome!!!

I’m glad that I was able to assist you.

@BrianJ - Thank You so much for the kind words of appreciation.

Thanks and Warm Regards,
Harsh