Actual Working days and month days

[mrslyfox]

Hello,

have a formula  = (actual month sales/act month working days) * working days in month.

Model contain Calendar table with key whitch indicate is it a working days or not [WordkingDayYN]

And Fact Sales tables by dates

I need to create a Chart with Sales on Y and MonthNames on X for selected year.

[v-yulgu-msft]

Hi @mrslyfox,

Not very clear about the difference between the two highlighted parts.

Based on my assumption, the former one means the actual working days per month depend on the values of [WorkingDayYN], the latter one means the number of dates each month.

If that is the case, please try below measures:

Month Total Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[LINK_Date].[Month] )
)

Woring day per month =
CALCULATE (
    COUNTROWS ( 'Calendar' ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[LINK_Date].[Month] )
)

Actual Woring days =
CALCULATE (
    SUM ( 'Calendar'[WorkingDay] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[LINK_Date].[Month] )
)

Act Sales = [Month Total Sales]/[Actual Woring days]*[Woring day per month]

Please refer to the uploaded .pbix file for details.

Best regards,
Yuliana Gu

[mrslyfox]

@v-yulgu-msft

Example:

Today: 26 - October
Act Sales = MTD Sales for October = 22 645 684.89

October Calendar days: 31

working days in month = October Total Working days Total = 22

act month working days = October MTD Working days = 19

resuted formula: = 22 645 684.89 / 19 * 22 = 26 221 319.34

[mrslyfox]

up

[v-yulgu-msft · Accepted solution]

Hi @mrslyfox,

Please try:

MTD Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER ( 'Calendar', 'Calendar'[LINK_Date].[Day] <= DAY ( TODAY () ) )
)

MTD working days =
CALCULATE (
    SUM ( 'Calendar'[WorkingDay] ),
    FILTER ( 'Calendar', 'Calendar'[LINK_Date].[Day] <= DAY ( TODAY () ) )
)

Working days total per month =
CALCULATE (
    SUM ( 'Calendar'[WorkingDay] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[LINK_Date].[Month] )
)

Act Sales = [MTD Sales]/[MTD working days]*[Working days total per month]

Best regards,
Yuliana Gu

[SivaMani]

@mrslyfox,

Create a relationship between the tables. 

Add the required fields to the chart.

[mrslyfox]

@SivaMani

relationship exist, how DAX measure will looks like ?

[SivaMani]

@mrslyfox,

Just to share some sample data and we will proceed further.

[mrslyfox]

@SivaMani

Download link 
https://we.tl/V3xqrlA9qV

[mrslyfox]

up