Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
4 years ago
Solved

Sales Amount Divided by Cumulative Sum Days Using DAX Measure

Hi Team,

 

I got a very interesting calculation/Problem from my client, they want to divied the "Sales Amount" / "Cumulative Sum days".

 

For Example:

1. Jan Sales = [Sales Amt]/31days.

2. Feb Sales = [Sales Amt]/59days I.e [28 days of feb + 31 days of Jan]

3.Mar sales = [Sales Amt]/90days I.e [31 days of Mar + 28 days of feb +31 days of Jan]

4.Apr sales = [Sales Amt]/120days I.e [30 days of Apr +31 days of Mar + 28 days of feb +31 days of Jan] etc..

..................................................................

12.Dec sales = [Sales Amt]/360days I.e [31 days of Jan + 59 days of Feb + 90 days of Mar + 120 days of Apr + 151 days of ay + 181 days of Jun + 212 days of Jul + 243 days of Aug + 273 days of Sep + 304 days of Oct + 334 days of Nov + 365 days of Dec]

 

Moreover the measure has to account for leap year for the month of Feb. Below are the screenshot for Power and an example within the Excel shown.

Please I request all my Power BI DAX Expert to assist me with this issue Thanks in Advacne.

 

 

amitchandak

Greg_Deckler

 

Regards

Suhel

 

Need Help

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi,  Suhel_Ansari 

    Try the following formulas:

     

    Cumulative Sum days = 
VAR day1 =
    DATE ( MAX ( 'Table'[Date].[Year] ), 1, 1 ) //first day of the year
VAR day2 =
    EOMONTH ( MAX ( 'Table'[Date] ), 0 ) //last day of the month
RETURN
    DATEDIFF ( day1, day2, DAY )
    Result = [Sales Amt]/[Cumulative Sum days]

     

    Please check my sample file for  details.

    Best Regards,
    Community Support Team _ Eason

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Suhel_Ansari Not sure of your underlying data model but if you have a calendar table you could do a simple COUNTROWS to get your denominator:

    Denominator Measure = 
  VAR __MaxDate = MAX('Calendar'[Date])
RETURN
  COUNTROWS(FILTER('Calendar',[Date]<=__MaxDate))