Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Basava
Frequent Visitor

Help in DAX

‎01-29-2025 12:12 AM

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

 

Message 1 of 3
199 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎01-31-2025 02:18 AM

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.

vxuxinyimsft_0-1738318534724.png

 

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:

vxuxinyimsft_1-1738318681044.png

 

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.

View solution in original post

Help in DAX.pbix
Message 3 of 3
136 Views
0
2 REPLIES 2
Anonymous
Not applicable

‎01-31-2025 02:18 AM

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.

vxuxinyimsft_0-1738318534724.png

 

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:

vxuxinyimsft_1-1738318681044.png

 

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.

Help in DAX.pbix
Message 3 of 3
137 Views
0
bhanu_gautam
Super User
Super User

‎01-29-2025 02:37 AM

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 3
169 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All