topic Re: Help in DAX in DAX Commands and Tips

Help in DAX

Basava — Wed, 29 Jan 2025 08:12:59 GMT

Hi , Team

Here i am addressing my requirement, need to use DAX (Data Analysis Expressions) to calculate the counts based on the selected "Reported Date" and the corresponding "Created Date" and "W/H" (Working Day or Holiday). From my description, it looks like what i want to:

When the "Reported Date" is selected, i want to calculate the count of count column based on the "Created Date" column.
i want to handle both "Holiday" and "Working Day" cases for filtering.
The calculation should dynamicall
y adjust based on whether the "Reported Date" is a working day, holiday, or a specific day of the week (e.g., Monday).

Requirment

If Reporting reporting date is selected and it is refering to "Holiday" , it calculates the counts for the two previous days (e.g., for 26th December selected from reporting date, it will consider 25th and 24th December).
If reporting date is selected and its refering to "Working Day" , it calculates the count for the previous day (e.g., for 24th December, it will consider 23rd December).

If the "Reported Date" is selected and its a Monday, it calculates the count for the previous Saturday and Sunday (e.g., for 23rd December, it will consider 22nd and 21st December).

Please help me in dax and insering the columns as well and A and B table is there both connected o Createdate and reported date

B Table:

Reported Date

12/1/2024

12/2/2024

12/3/2024

12/4/2024

12/5/2024

12/6/2024

12/7/2024

12/8/2024

12/9/2024

12/10/2024

12/11/2024

12/12/2024

12/13/2024

12/14/2024

12/15/2024

12/16/2024

12/17/2024

12/18/2024

12/19/2024

12/20/2024

12/21/2024

12/22/2024

12/23/2024

12/24/2024

12/25/2024

12/26/2024

12/27/2024

12/28/2024

12/29/2024

12/30/2024

12/31/2024

A Table :

Created Date	W/H	Count
12/1/2024	Working Day	A-3
12/2/2024	Working Day	AA-1
12/24/2024	Working Day	AA-2
12/4/2024	Working Day	AA-3
12/5/2024	Working Day	AA-4
12/6/2024	Working Day	AA-5
12/7/2024	Working Day	AA-6
12/8/2024	Working Day	AA-7
12/9/2024	Working Day	AA-8
12/10/2024	Working Day	AA-9
12/11/2024	Working Day	AA-10
12/12/2024	Working Day	AA-11
12/13/2024	Working Day	AA-12
12/14/2024	Working Day	AA-13
12/15/2024	Working Day	AA-14
12/16/2024	Working Day	AA-15
12/17/2024	Working Day	AA-16
12/18/2024	Working Day	AA-17
12/19/2024	Working Day	AA-18
12/20/2024	Working Day	AA-19
12/21/2024	Working Day	AA-20
12/22/2024	Working Day	AA-21
12/23/2024	Working Day	AA-22
12/24/2024	Working Day	AA-23
12/25/2024	Holiday	AA-24
12/26/2024	Working Day	AA-25
12/27/2024	Working Day	AA-26
12/28/2024	Working Day	AA-27
12/29/2024	Working Day	AA-28
12/30/2024	Working Day	AA-29
12/31/2024	Working Day	AA-30

Re: Help in DAX

bhanu_gautam — Wed, 29 Jan 2025 10:37:12 GMT

Basava ,Try using first create a calculated column to identify holiday and weekdays

Create a calculated column in the A Table

IsHoliday = IF(A[W/H] = "Holiday", TRUE(), FALSE())

create a measure that calculates the count based on the selected "Reported Date" and the conditions specified.

DynamicCount =
VAR SelectedDate = SELECTEDVALUE(B[Reported Date])
VAR IsSelectedDateHoliday = CALCULATE(MAX(A[IsHoliday]), A[Created Date] = SelectedDate)
VAR IsSelectedDateMonday = WEEKDAY(SelectedDate, 2) = 1
VAR PreviousDay = SelectedDate - 1
VAR TwoDaysBefore = SelectedDate - 2
VAR PreviousSaturday = SelectedDate - WEEKDAY(SelectedDate, 2) - 1
VAR PreviousSunday = SelectedDate - WEEKDAY(SelectedDate, 2)

RETURN
IF(
IsSelectedDateHoliday,
CALCULATE(SUM(A[Count]), A[Created Date] IN {TwoDaysBefore, PreviousDay}),
IF(
IsSelectedDateMonday,
CALCULATE(SUM(A[Count]), A[Created Date] IN {PreviousSaturday, PreviousSunday}),
CALCULATE(SUM(A[Count]), A[Created Date] = PreviousDay)
)
)

You can now use the DynamicCount measure in your report to get the desired counts based on the selected "Reported Date".

Re: Help in DAX

Anonymous — Fri, 31 Jan 2025 10:18:31 GMT

Hi Basava

Thanks for the reply from bhanu_gautam .

Basava , if you want to display dynamically, you need to create a measure. The following test is for your reference, there is no relationship between the two tables.

Measure:

Measure = VAR _selected = SELECTEDVALUE(B[Reported Date]) VAR _Is = CALCULATE(MAX(A[W/H]), FILTER(A, [Created Date] = _selected)) VAR _P1 = _selected - 1 VAR _P2 = _selected - 2 VAR _result = IF(_Is = "Holiday" || WEEKDAY(MAX('A'[Created Date]), 2) = 1, CALCULATE(COUNT(A[Count]), FILTER(A, [Created Date] < _selected && [Created Date] >= _P2)), CALCULATE(COUNT(A[Count]), FILTER(A, [Created Date] < _selected && [Created Date] >= _P1))) RETURN _result

Output:

If you still have questions, please let me know.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.