Cumulative MTD rates whit different dimensions aggregation criterion

[AlexFulgosi]

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.

[amitchandak]

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

[AlexFulgosi]

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

[v-shex-msft · 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