Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |