cancel
Showing results for
Did you mean:
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
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.
4 REPLIES 4
Helper II

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

Community Support

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

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 !!

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it Out!

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