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

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