Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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?
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
Solved! Go to Solution.
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
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
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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |