cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cumulative Sales in the latest month

Hi everyone, 

 

I'm fairly new to DAX and am trying to create a measure for cumulative sales in the latest month (and have tried a bunch of solutions provided on this forum) but I'm still not getting the result I'm looking for. 

 

Could someone let me know what I'm doing wrong please? 

 

 

SalesCumu_MTD = 
VAR mth = 4
    --MONTH(LASTDATE('Fact'[InvoiceDate]))
VAR yr = 2020
    --YEAR(LASTDATE('Fact'[InvoiceDate]))
VAR dayofmth = 21
    -- DAY(LASTDATE('Fact'[InvoiceDate]))
RETURN
    CALCULATE (
        SUM ( 'Fact'[SalesValue]),
        FILTER (
            ALL(DimPeriod),
            DimPeriod[FiscalMthNo] = mth
            && DimPeriod[FiscalYrNo] = yr
            && DimPeriod[FiscalDayNo_OfMth] <= dayofmth
        )
    )

 

 

 

I understand that if I don't include the ALL(DimPeriod) I'll get the daily sales of each day in that month. But when I include ALL, it's giving me the total sales in that month... which actually makes sense but it's not what I'm looking for. So my question is, how do I get the cumulative daily sales for each day? 

 

Capture.JPG

 

Any help would be appreciated! Thank you in advance! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Cumulative Sales in the latest month

@psiripun 

 

I have modified the logic.

 

Capture.JPG

You have to turn on the date hierarchy 

Capture.JPG

MTD_sales = CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[InvoiceDate].[Date]))
//if you are not using data hierarchy .[Date] not required. But InvoiceDate should be date type
MTDSales = TOTALMTD(SUM('Table'[Sales]),'Table'[InvoiceDate].[Date])
//if you are not using data hierarchy .[Date] not required. But InvoiceDate should be date type



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

 

 

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Cumulative Sales in the latest month

@psiripun 

 

Not sure about your existing data and expected output.

I have prepared a sample dataset and measures.

 

Monthly Sales = SUM('Table'[Sales])
YTD_sales = CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[InvoiceDate].[Date]))
YTDSales = TOTALYTD(SUM('Table'[Sales]),'Table'[InvoiceDate].[Date])

Capture.JPG

 

Let me know if you have any queries.



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Highlighted
Frequent Visitor

Re: Cumulative Sales in the latest month

@nandukrishnavs 

 

Thanks so much for your suggestion! I tried to replicate the formulas with my data but I'm still not getting the result I'm looking for. 

 

Expected Output: Cumulative daily sales for the latest month, April 2020 

 

Capture.JPG

 

Test_Sales_MTD = CALCULATE(SUM('Fact'[SalesValue]),DATESMTD('Fact'[InvoiceDate]))
Test_Sales_MTD2 = TOTALMTD(SUM('Fact'[SalesValue]),'Fact'[InvoiceDate])
Sales_MTD = 
VAR mth = 4
VAR yr= 2020
RETURN
    CALCULATE (
        SUM ( 'Fact'[SalesValue]),
        FILTER (
            (DimPeriod),
            DimPeriod[FiscalMthNo] = mth
                && DimPeriod[FiscalYrNo] = yr
        )
    )

 

There are a few issues here: 

1. The values are of the sales in each day; they're not added cumulatively. 

2. Some days where there is no sales value (such as the 5th or 6th of April), the formula is getting the sales value from the previous month instead of leaving the row blank. 

 

Could you pls help advise what I'm missing? 

 

Thank you so much! 

Highlighted
Community Champion
Community Champion

Re: Cumulative Sales in the latest month

@psiripun 

 

I have modified the logic.

 

Capture.JPG

You have to turn on the date hierarchy 

Capture.JPG

MTD_sales = CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[InvoiceDate].[Date]))
//if you are not using data hierarchy .[Date] not required. But InvoiceDate should be date type
MTDSales = TOTALMTD(SUM('Table'[Sales]),'Table'[InvoiceDate].[Date])
//if you are not using data hierarchy .[Date] not required. But InvoiceDate should be date type



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

 

 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors