Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Folks,
I'm trying to create a measure based on this mathematical formula:
Measure = (Total Accounts Recievable in the Month * # of days in the given month) / Total Sales in the Month.
What I have so far:
Solved! Go to Solution.
Hi @GarlonYau
To count # of days per month
Days In Month = DAY(DATE(YEAR(T25[Date]),MONTH(T25[Date])+1,1)-1)
Cheers!
A
If you have a calendar table, a simply DISTINCTCOUNT can work.
Ex:
Days in quarter -->
Add a column to your date table.
Days in Q = VAR FirstDateVisible = CALCULATE ( MIN ( 'Date'[Date] ) ) VAR FirstYearVisible = YEAR ( FirstDateVisible ) VAR FirstQuarterVisible = QUOTIENT ( MONTH ( FirstDateVisible ) - 1, 3 ) VAR DaysInQuarter = FILTER ( ALL ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = FirstYearVisible && QUOTIENT ( MONTH ( 'Date'[Date] ) - 1, 3 ) = FirstQuarterVisible ) VAR FirstDayInQuarter = MINX ( DaysInQuarter, 'Date'[Date] ) VAR _Begin = CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Date] = FirstDayInQuarter ) VAR LastDateVisible = CALCULATE ( MAX ( 'Date'[Date] ) ) VAR LastYearVisible = YEAR ( LastDateVisible ) VAR LastQuarterVisible = QUOTIENT ( MONTH ( LastDateVisible ) - 1, 3 ) VAR LastDayInQuarter = MAXX ( DaysInQuarter, 'Date'[Date] ) VAR _End = CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Date] = LastDayInQuarter ) RETURN DATEDIFF(_Begin,_End,DAY)
try, this is for line level
calculate((sum(Sales[Sales Amount])*DISTINCTCOUNT(Sales[Sales Date])) )
distinct dates, if data is continuous
How would I go about incorporating that into my existing equation?
something like that
calculate(divide((SUM('BalanceSheet'[Accounts Receivable])*DISTINCTCOUNT(BalanceSheet[ Date])),SUM('IncomeStatement'[Sales])) )
Hi @GarlonYau
To count # of days per month
Days In Month = DAY(DATE(YEAR(T25[Date]),MONTH(T25[Date])+1,1)-1)
Cheers!
A
@Anonymous
I ended up getting it by using your new column!
Thank you so much!
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |