Turn YTD logic with quarterly data (fill the gaps)

[Rapier]

Hi,

I faced with the following issue, where I should find any solution to "turn back" the logic of YTD:

There are some countries, where we received data quarterly (Q1 --> in April, Q2 --> in July, etc.).

I should show YTD, but not with the common logic. 

The request is: if Q1/2021 data is received, it should be allocated to Jan/Feb/Mar of 2021, but not to Mar/Apr/May of 2021.

As an example there is the following screenshot.

I think it will be confusing for some users (data change for past months after three month), but at the end the client wouldn't like to see blank month between december (last data for a year) and march (first data for an other one).

I feel it absolutely uncommon request based on what I experienced, but they are very uncomplying.

Is it possible to solve it somehow with DAX, or should we transform the data (and/or data model) for this?

Thank you for your help!

[v-jianboli-msft · Accepted solution]

Hi Rapier,

Please try:

Request Logic =

var _c = CALCULATE(SUM('Values'[Data]),FILTER('Values',MONTH('Values'[Month])<MONTH( EARLIER('Date'[Month]))+3 &&YEAR([Month])=YEAR( EARLIER([Month]))))

var _d = CALCULATE(MAX('Values'[Data]),FILTER('Values',MONTH([Month])=3))

return IF(MONTH('Date'[Month])<3,_d,_c)

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Rapier , Not very clear. I worked on allocation logic for yearly data, but the same can be done on quarterly data

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Rapier]

@amitchandak Thanks for your help, and maybe the solution at the end will be something similar, but make this things clear I attach an Excel table, where I try to show you the exact request: 

 Otherwise I can't attach the original file, but this part of data is very simple, like this:

YTD = TOTALYTD(SUM('Values'[Data]),'Date'[Months])