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 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!
Solved! Go to Solution.
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 🙂
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 🙂
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!
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 🙂
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |