Number of days in the period

[GarlonYau]

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: 

DIVIDE( SUM('BalanceSheet'[Accounts Receivable]) * XXXXX, SUM('IncomeStatement'[Sales]),0)

What I am struggling with is filling out what should go in the XXXXX portion. This would be a DAX formula for the number of days in the month (or in the time period - if it's going to respond drilling up and down in a visual)

Example:

Feburary = (Total Accounts Recievable in the Month * 28) / Total Sales in the Month.

March = (Total Accounts Recievable in the Month * 31) / Total Sales in the Month.

Q1  = (Total Accounts Recievable in the Month * 90) / Total Sales in the Month.

Thanks in advance for your help!

Cheers

[Anonymous]

If you have a calendar table, a simply DISTINCTCOUNT can work.

Ex:

Calendar - Count Days = DISTINCTCOUNT('Calendar'[Date])

[Anonymous]

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)

[amitchandak]

try, this is for line level

calculate((sum(Sales[Sales Amount])*DISTINCTCOUNT(Sales[Sales Date]))
)

distinct dates, if data is continuous

[GarlonYau]

How would I go about incorporating that into my existing equation?

[amitchandak]

something like that

calculate(divide((SUM('BalanceSheet'[Accounts Receivable])*DISTINCTCOUNT(BalanceSheet[ Date])),SUM('IncomeStatement'[Sales]))
)

[Anonymous · Accepted 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

[GarlonYau]

@Anonymous 

I ended up getting it by using your new column!

Thank you so much!

Days Sales Outstanding = DIVIDE(SUM('Financial Statements - BALANCE SHEET'[(Group) Accounts Receivable])*AVERAGE('Date'[Days In Month]), SUM('Financial Statements - INCOME STATEMENT'[(Subgroup) Sales]),0)