Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Giorgi1989
Advocate II
Advocate II

Actual working days DAX formula refinement needed

Dear All,

 

In my sales report I do have 'actual sales' and 'linear sales estimates' columns. For the 'linear sale estimates' I use the basic formula: 

Linear sales est. = (Merge[Act sales])*((Dates[Total Actual Monthly Working Days])/Dates[Actual working days until prev day]). 

 

Actual working days until prev day = Calculate(

COUNTROWS('Dates'),
DATESBETWEEN(Dates[Date],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),TODAY()-1),
'Dates'[IsWorkingDay]=TRUE()
)
 
TODAY()-1),  - Because the data always displays figures from the previous day I always need to show what were actual working days from the start of the month to yesterday. So for example, on July 1, the figures would show act sales as of June 30. 
 
The problem - With the current formula I run into a problem on the 1st of every month, when TODAY()-1) stops making sense. 
 
Is there anyway, I can make so that on the 1st day of calendar month, my calculation - (Merge[Act sales])*((Dates[Total Actual Monthly Working Days])/Dates[Actual working days until prev day]) - would show both the 'Total Actual Monthly Working Days' and 'Actual working days until prev day' from previous month?
 
So in practice, on December 1, the calculation would show: Act Sales*((Total actual Monthly Working Days of previous month (22)/Actual working days until prev day (22). As a result, my actual sales would be the same as the linear sales estimates, given that it is the last day of the month. 

Any help would be highly appreciated.
 
Best,
Giorgi
 
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Giorgi1989 ,

Please update the formula of measure [Actual working days until prev day] as below and check whether it can get your expected result...

Note: The part with red font is updated one.

Actual working days until prev day =
CALCULATE (
    COUNTROWS ( 'Dates' ),
    DATESBETWEEN (
        Dates[Date],
        IF (
            DAY ( TODAY () ) = 1,
            DATE ( YEAR ( TODAY () - 1 )MONTH ( TODAY () - 1 )1 ),
            DATE ( YEAR ( TODAY () )MONTH ( TODAY () )1 )
        ),
        TODAY () - 1
    ),
    'Dates'[IsWorkingDay] = TRUE ()
)

If the above one can't help you get the correct result, please provide some sample data with Text format and your expected result with calculation logic and special examples. Thank you.

Best Regards

Community Support Team _ Rena
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

4 REPLIES 4
Giorgi1989
Advocate II
Advocate II

Thank you for the feedback and support! Will try those out!

Hi @Giorgi1989 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

Hi @Giorgi1989 ,

Please update the formula of measure [Actual working days until prev day] as below and check whether it can get your expected result...

Note: The part with red font is updated one.

Actual working days until prev day =
CALCULATE (
    COUNTROWS ( 'Dates' ),
    DATESBETWEEN (
        Dates[Date],
        IF (
            DAY ( TODAY () ) = 1,
            DATE ( YEAR ( TODAY () - 1 )MONTH ( TODAY () - 1 )1 ),
            DATE ( YEAR ( TODAY () )MONTH ( TODAY () )1 )
        ),
        TODAY () - 1
    ),
    'Dates'[IsWorkingDay] = TRUE ()
)

If the above one can't help you get the correct result, please provide some sample data with Text format and your expected result with calculation logic and special examples. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@Giorgi1989 , Work day as measures

 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Date[Date]),Today() -1),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 

a new column

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Date[Date],Ttoday() -1 ),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors