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
Guilher_Moura19
Frequent Visitor

Calculate and divind values by working days

Hi, everyone

 

I have a table with CALENDARAUTO( ) and i added a column WorkDay = 1 when true, 0 when false

 

Using another table, i have the sale day and the price of the sale, but i'd to another columns that says a big value of "month sales expectation" that is declared in the first day of the month.

 

month sales expectation.png

But the other days is declared by 0. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How can i replicate or create a measure/column to take this Total of Month Expectation divided by the count of Working Days in equal parts, to show something like this?

 

daily sales expectation.png

 

 

 

 

I create this table on excel and i divide the total of sales manually by the count of the Working days.

 

 

 

 

 

 

 

 

 

 

 

Thank you all,

 

Best Regards

1 ACCEPTED SOLUTION
MarcoPessina
Resolver IV
Resolver IV

Hello @Guilher_Moura19 ,

you can also try this measure:



CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
ALL ( 'Date' ),
MONTH ( 'Date'[Date]) ? MONTH ( SELECTEDVALUE ( 'Date'[Date] )
&& Year ( 'Date'[Date] ) - Year ( SELECTEDVALUE ( 'Date'[Date] )
&& WEEKDAY('Date'[Date]) <> 6 && WEEKDAY('Date'[Date]) <> 7
)
)
VAR SalesExpMonthValue ? CALCULATE(
max(Sales[MonthSalesExp]),PARALLELPERIOD('Date'[Date],0,MONTH))
Return
DIVIDE(SalesExpMonthValue,WorkingDaysMonth,0)

I hope it helps.

Bless you

Frame of reference

View solution in original post

3 REPLIES 3
MarcoPessina
Resolver IV
Resolver IV

Hello @Guilher_Moura19 ,

you can also try this measure:



CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
ALL ( 'Date' ),
MONTH ( 'Date'[Date]) ? MONTH ( SELECTEDVALUE ( 'Date'[Date] )
&& Year ( 'Date'[Date] ) - Year ( SELECTEDVALUE ( 'Date'[Date] )
&& WEEKDAY('Date'[Date]) <> 6 && WEEKDAY('Date'[Date]) <> 7
)
)
VAR SalesExpMonthValue ? CALCULATE(
max(Sales[MonthSalesExp]),PARALLELPERIOD('Date'[Date],0,MONTH))
Return
DIVIDE(SalesExpMonthValue,WorkingDaysMonth,0)

I hope it helps.

Bless you

Frame of reference

Thank you, @MarcoPessina 

 

I ran your code and got some errors, but I made some minor changes to the logic and everything went well.
 
Thank you!
 
Best regards,
Guilherme Moura
Mariusz
Community Champion
Community Champion

Hi @Guilher_Moura19 

 

Try something like this.

Sales split witin Month = 
VAR __valueToSplit = 
    CALCULATE(
        [Sales],
        ALL( 'Calendar' ),
        VALUES( 'Calendar'[Year Month] )
    )
VAR __countWorkingDays = 
    CALCULATE(
        COUNTROWS( 'Calendar' ),
        ALL( 'Calendar' ),
        VALUES( 'Calendar'[Year Month] ),
        VALUES( 'Calendar'[Is Weekend] ),
        KEEPFILTERS( 'Calendar'[Is Weekend] = FALSE() )
    )
RETURN 
DIVIDE( __valueToSplit, __countWorkingDays )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

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.