Actual working days DAX formula refinement needed

[Giorgi1989]

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

[Giorgi1989]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[amitchandak]

@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