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

Cumulative MTD rates whit different dimensions aggregation criterion

Hi everyone,

 

I'm trying to get a DAX measure to cumulate a series of montly rates obtained in a single, selected, fiscal year.

 

I'm working with two dimensions (geography and dates) and a fact table that shows 2 quantities (lets's call it A and B), for each month and country.

 

I basically need to show 2 graphs for a selected fiscal year:

 

-One contains the trend of total A / total B for each month, and it should be possible to be filtered by country/region/area and selected months, and the same trend from the previous year.

I've simply implemented those two measures by using:

 

ABRate := TOTALMTD( Sum(A) / Sum(B), DimDates[Date])

 

ABRatePY := TOTALMTD( Sum(A) / Sum(B), SAMEPERIODLASTYEAR(DimDates[Date]))

 

 

-The other one should display the cumulative trend of the previous monthly quantities, i.e. in the third month ABRate of first + ABRate of second + ABRate of the tird, and NOT the cumulative sum of A / cumultive sum of B. Also, it must be filtered by the sames filters. Same for previous year.

 

Keep in mind that, if a region contains several countries, ABRate is the sum of A considering all selected countries / sum of B considering all selected countries, not the sum of ABRates in single countries. The cumulative trend keeps this structure in every month but then it sum up the singles rates obtained in every single month, so is NOT the sum of A in all selected month / sum of B in all selected month, but sum of singles tot A / tot B.

So, date dimension will sum A/B and geography will sum A and B.

 

Any idea? (I actually made it work by using 12 temporary variables with the right value for each month and a conditional sum of those vriables, but i would prefer to find out a more efficent solution). Thank you.

 

 

1 ACCEPTED SOLUTION

HI @AlexFulgosi,

Current you can't do custom on time intelligence functions, I'd like to suggest you try to date function to manually define filter range to calculate:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

I did not get you completely. But cumulative of months you can get with YTD

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Or like

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,month))))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Yes, the difficult thing here is not to obtain cumulative totals in each month, but to sum those total and, at the same time, keep the possibility to filtering by country and months, where:

 

- Multiples contries selected will delimitate an area where quantity A and B are cumuleted as YTD till the current selected date

 

- Each month will define a single rate A/B and all the rates in the selected months are to be summed (so multiples month don't have to cumulate A and B as YTD but to cumulate A/B, keeping possibility to filtering by country).

 

So, a  YTD logic doesn't work for splitting the singles A/B in each month

HI @AlexFulgosi,

Current you can't do custom on time intelligence functions, I'd like to suggest you try to date function to manually define filter range to calculate:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!