Cumulative Sales in the latest month

[psiripun]

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? 

Any help would be appreciated! Thank you in advance! 

[nandukrishnavs]

@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])

Let me know if you have any queries.

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

[psiripun]

@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 

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! 

[nandukrishnavs · Accepted solution]

@psiripun 

I have modified the logic.

You have to turn on the date hierarchy 

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 🙂