cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Giorgi1989
Helper II
Helper 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
yingyinr
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
Helper II
Helper 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.
yingyinr
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!