Forum Discussion

Basava's avatar
Basava
Frequent Visitor
1 year ago
Solved

Help in DAX

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 DateW/HCount
12/1/2024Working DayA-3
12/2/2024Working DayAA-1
12/24/2024Working DayAA-2
12/4/2024Working DayAA-3
12/5/2024Working DayAA-4
12/6/2024Working DayAA-5
12/7/2024Working DayAA-6
12/8/2024Working DayAA-7
12/9/2024Working DayAA-8
12/10/2024Working DayAA-9
12/11/2024Working DayAA-10
12/12/2024Working DayAA-11
12/13/2024Working DayAA-12
12/14/2024Working DayAA-13
12/15/2024Working DayAA-14
12/16/2024Working DayAA-15
12/17/2024Working DayAA-16
12/18/2024Working DayAA-17
12/19/2024Working DayAA-18
12/20/2024Working DayAA-19
12/21/2024Working DayAA-20
12/22/2024Working DayAA-21
12/23/2024Working DayAA-22
12/24/2024Working DayAA-23
12/25/2024HolidayAA-24
12/26/2024Working DayAA-25
12/27/2024Working DayAA-26
12/28/2024Working DayAA-27
12/29/2024Working DayAA-28
12/30/2024Working DayAA-29
12/31/2024Working DayAA-30

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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".

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.