Dax for Current Week & Previous Week

[Anonymous]

Hi,

My requirement got changed like below.

I have a Date slicer and my week starting from Monday to Sunday.

If user selects Today (May 24,2021) then i should be able to see the data only for Current week.

If user selects the future dates in the current week let's say May26th then i should be able to get the sum of May24,25 and May26th.

if user wants select the past dates like May 21st then i should be able to see the data for whole complated week i.e May 17th to May 23rd. 

Any help (Super Users) on the Dax code would be appreciated

Below is the code somehow i have written for the below code and how can i concatenate current week dax

Weekly =
var _selectedDate = MAX(Table1[DayDate])
var _endOfWeekDate = _selectedDate + 7 - WEEKDAY(_selectedDate,2)
RETURN CALCULATE(Sum(Table1[TH]),Table1[DayDate] <= _endOfWeekDate

Table1 is nothing but my main table.

Thanks,
Venky

[MFelix]

Hi @Anonymous ,

Is the understanding of your requirements below correct?

User selection is Today = current week data until today

User selection higher than today = data from today until the day selected

User selection lower than today = full week of the day selected

Just a couple of questions how do you handle the selection of a day of the current week but lower than today? (do you pick up all the values of the week or only the ones until today or the selected day?

Are you abble to choose values after the current week? for example august date what values will you get for that selection?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Hi @MFelix Apologies for late reply. Thanks for your response.

Hi Pat @mahoneypat . Thanks so much my issue resolve with your code and have done little bit changes as per the requeirement.

Regards,

PV

[mahoneypat · Accepted solution]

You could add a variable to calculate the most recent monday (relative to the MAX date), and then calculate your measure between the max date and that Monday date.

Weekly =
VAR _selectedDate =
    MAX ( Table1[DayDate] )
VAR _endOfWeekDate =
    _selectedDate + 7
        - WEEKDAY ( _selectedDate, 2 )
VAR lastMonday =
    CALCULATE (
        MAX ( Table1[DayDate] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            Table1[DayDate] <= _selecteddate
                && WEEKDAY ( Table1[DayDate] ) = 2
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[TH] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            Table1[DayDate] <= _endOfWeekDate
                && Table1[DayDate] >= lastMonday
        )
    )

Pat

[Anonymous]

Hi Pat,

I tried to breakdown this formula but seems like not getting what iam expecting. Could you please help me to find exact one

 05/27/2021 is my Max Date and also it falls in Current Week (my week:Monday to Sunday) so iam expecting the result is sum of (05/24/2021+05/25/2021+05/26/2021+05/27/2021)

if user select the any date in completed week (Completed week 05/17/2021 to 05/23/2021) then i should be able to get sum of whole completed week

Ex: if i select 05/23/2021 then sum of 05/17/2021 to 05/23/2021

and also iam i tried below logic but not working

Z_Selected Dte =

Var _SDate = SELECTEDVALUE(Table1[DayDate])

Var _EndDate = _SDate-WEEKDAY(_SDate,2)

Return

CALCULATE(

SUM(Table1[TH]),

FILTER(ALL(Table1[DayDate]),

[z_Max_DayDate]<=_EndDate

)

)

Regards,

Venky

[mahoneypat]

Not sure why that didn't work.  Do you have other date related columns in the visual?  In any case, another approach to consider is to add a DAX column with the week ending date and use that in your slicer.

WeekEnding = Table1[DayDate] + 8 - WEEKDAY(Table1[DayDate])

Pat

[Anonymous]

@mahoneypat Yes its there and the column name is weekdate in the line & Clustered Column chart visual. and also Weekdate is filtered as TOP 12 Latest Weekdate.

DayDate is my Slicer.

Pat, If you can see the attached screenshot yesterday, iam not using any dates apart from DayDate in my Testing Table Visual and even iam not able to get the data as per my requirement.

Thanks,

Venky

[Anonymous]

@mahoneypat Hi Pat,

Apologies not to apply proper filter.

I had a discussion with my client last week and your code was working as expected after applying Weekdate filter to get top 12 latest dates.

For Previous/Completed week (05/31/2021 to 06/06/2021) data is fine and only problem with current week. They wanted to have the data for available dates only like if data is available for June 7 & 8th and users selects 06/10/2021 and then only  then take those 2days sum and divide by 2. not to take sum of 4 days and divide by 4days. Basically with the above code summarization is fine and problem with to take only available dates data

With your above code iam using to create another same measure and only change is Sum(Quantity) instead of Sum(TH) and using these two measures to get percentage

Weekly_Qty =
VAR _selectedDate =
    MAX ( Table1[DayDate] )
VAR _endOfWeekDate =
    _selectedDate + 7
        - WEEKDAY ( _selectedDate, 2 )
VAR lastMonday =
    CALCULATE (
        MAX ( Table1[DayDate] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            Table1[DayDate] <= _selecteddate
                && WEEKDAY ( Table1[DayDate] ) = 2
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[QTY] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            Table1[DayDate] <= _endOfWeekDate
                && Table1[DayDate] >= lastMonday
        )
    )

Percentage = 

DIVIDE(DIVIDE([Weekly],1440,0),[Weekly_QTY],0)

Note: With the [Weekly_QTY] variable iam getting 7 (days) which i wanted to change based on selected date and avalable data for the dates

I have changed below variable but iam getting from the selected date is 4 which is nor correct.

Weekly_Qty =
VAR _selectedDate =
    MAX ( Table1[DayDate] )
VAR _endOfWeekDate =
    _selectedDate + 7
        - WEEKDAY ( _selectedDate, 2 )
VAR lastMonday =
    CALCULATE (
        MAX ( Table1[DayDate] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            Table1[DayDate] <= _selecteddate
                && WEEKDAY ( Table1[DayDate] ) = 2
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[QTY] ),
        FILTER (
            ALL ( Table1[DayDate] ),
            //Table1[DayDate] <= _endOfWeekDate

             Table1[DayDate] <= _selectedDate
                && Table1[DayDate] >= lastMonday
        )
    )

Thanks,

Venky

[Anonymous]

@MFelix @amitchandak @v-yalanwu-msft @mahoneypat 

Hello Super users!! Any help to tune the dax.

Regards,

Venky

[Anonymous]

Thanks Pat. I am checking your dax today and i will update after sometime.

Regards,

Venky

[Anonymous]

@MFelix is this possible at a time to implement current and prevous week data to show (Completed week). 

Regards,

Venky

[VijayP]

@Anonymous 

Use DATEADD Function to get previous days data!

you can refer this video https://www.youtube.com/watch?v=dLON3mj0los&list=PLWQB3PEUJKRn1Y_s8whVWEoPPZa6Wm8B6&index=52