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.
I am trying to write a cummlative sum for date range that looks like this
date | Cummlative Sum 2020 | Cummlative Sum 2015 |
2020 - June | 100 | 50 |
2020 - July | 200 | 100 |
2020 - August | 250 | 150 |
Is it possible to write a cummalative sum of a year over another year in dax without using Date add and so i can just define the year as exactly 2019. The reason why i dont want to use Date add or any other (Date - 5 years), is because it is dynamic and it will not be correct next year.
Is this possible in dax?
What i have done so far:
I have managed this calculation which gives a total but does not break down correctly for month
But it looks like this (doesnt give monthly breakdown).
date | Cummlative Sum 2020 | Cummlative Sum 2015 |
2020 - June | 100 | 150 |
2020 - July | 200 | 150 |
2020 - August | 250 | 150 |
Solved! Go to Solution.
Hi @Anonymous ,
According to my understand , you wan to sum Amount based on each month of years dynamically ,right?
In my opinion, you could use Matrix and Slicer like this:
Or use the following formula:
dateSlicer =
VAR _maxDate =
MAX ( 'Periods'[Date] )
VAR _minDate =
MIN ( 'Periods'[Date] )
RETURN
CALCULATE (
SUM ( Sales[Amount] ),
FILTER (
ALL ( Periods ),
'Periods'[Date] >= _minDate
&& 'Periods'[Date] <= _maxDate
&& 'Periods'[Date].[Month] = MAX ( 'Periods'[Date].[Month] )
)
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Anonymous ,
According to my understand , you wan to sum Amount based on each month of years dynamically ,right?
In my opinion, you could use Matrix and Slicer like this:
Or use the following formula:
dateSlicer =
VAR _maxDate =
MAX ( 'Periods'[Date] )
VAR _minDate =
MIN ( 'Periods'[Date] )
RETURN
CALCULATE (
SUM ( Sales[Amount] ),
FILTER (
ALL ( Periods ),
'Periods'[Date] >= _minDate
&& 'Periods'[Date] <= _maxDate
&& 'Periods'[Date].[Month] = MAX ( 'Periods'[Date].[Month] )
)
)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
@Anonymous , Try like
measure =
var MinDate = DATE(2014,06,01)
var MaxDate = DATE(2015,05,31)
return
calculate(SUM(Sales[Amount]), FILTER(All(Date), Date[DAte] <=Max(Date[date]) && Date[Date] >=MinDate && Date[Date] <= MaxDate))
I think datesytd should also work like
YTD Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD('Date'[Date],"5/31"))
5 YTD back Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD(dateadd('Date'[Date],-5,Year),"5/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Thanks for the response,
Is there anyway to avoid using a dynamic calculations like Date add?
YTD Sales: CALCULATE (SUM (Sales [Sales]), DATESYTD ('Date' [Date], "5/31"))
5 YTD Overdue Sales: CALCULATE (SUM (Sales [Sales]), DATESYTD (dateadd (' Date '[Date], - 5, Year), "5/31"))
Instead of using Dateadd use just the year 2019?
Hi @Anonymous ,
Not really sure why you cannot use a filter on the Year and the Month, but I am not sure what your model looks like, assuming you have a Month column in your periods table you could try the formula below
var MinDate = DATE(2014,06,01)
var MaxDate = DATE(2015,05,31)
return CALCULATE(SUM(Sales[Amount]),
FILTER(ALL ( Periods[Date] ), (Periods[Date] <= MAX ( Periods[Date]) && Periods[Date] >= MinDate && [Date] <= MaxDate))&& Periods[Month] = MAX(Periods[Month]) )
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |