Decreasing order of cummulative totals in DAX depending on months?

[hari_bi]

Hello All,

Please find the above table:

Query : Base 1 is null/blank  in any month then i need to sum the Base 2 values from that null month to till last month of month of that year and need to display the value in new column called Achievement.

Example  1 :-  Jan-18 base 1 is null, so i need to add Base 2 values from Jan-18 to Jun-18 ( 100 +200+200+100+150+200= 950)

next Feb-18 base 1 value is null again i need to add Base 2 values from Feb-18 to Jun-18 ( 200+200+100+150+200= 850)

Next time i need to add Base 2 values from Mar-18 to Jun-18 ( 200+100+150+200= 650) on so on ..... till the last month.

Need help to create Dax for the above requirement.

Thanks,

Hari.

[hari_bi]

Hello,

Please Find the below PBI File link for the same.

https://www.dropbox.com/s/dfxma4lcd76ucvs/PBI.pbix?dl=0

Thanks,

Hari.

[Ashish_Mathur · Accepted solution]

Hi,

Here is the measure i used

Achievement = if(ISBLANK([Base1]),CALCULATE([Base2],DATESBETWEEN('Date'[Date],MIN('Date'[Date]),CALCULATE(MAX('Date'[Date]),ALL('Date'[Year Month])))),BLANK())

Hope this helps.

[hari_bi]

Hi Ashish Mathur,

Much appreciated .

Thanks for the query.

Regards,

Hari.

[Ashish_Mathur]

You are welcome.