Cumulative for certain period with filters applied per period

[nataliag93]

Hi, please, I need some help with DAX to create a view in PowerBi.

I have a dataset containing contract date, volume and delivery date. I'd like to create a view where I could calculate the cumulative volume since the beggining of the dataset until the end of the month (or weekly even better) as if I had snapshots of the dataset.

 Then, I would be able to show the volume contracted since the beggining of the dataset to Mar/18, from the beggining until April/2018, from the beggining until May/2018 and goes on....

Once I have this, I would need to get a view where just the volume to be delivered in the upcoming months would be shown. Then, the idea would be to display the data as:

filter volume contracted until Mar/2018, show volume that will be delivered from Mar/2018 ownards per delivery period (current is March, First is April and goes on...)

filter volume contracted until Apr/2018, show volume that will be delivered from Apr/2018 ownards per delivery period (current is April, First is May and goes on...)

filter volume contracted until May/2018, show volume that will be delivered from May/2018 ownards per delivery period (current is May, First is Juneand goes on...)

The excel below show the representation that i'd need to create in PowerBi.

 Please, could someone help me the DAX to build this??

 THANKS IN ADVANCE!!!

[afzalphatan]

Hi... Hope the below soution does solve ur problem

You need to modify your data table a little bit (as shown in pic) create different measure for each Currnet, First....Fourth so on .. But that isn't difficult.

As highlighted in red circle.. you may change number to 3 for "Third" Measure and 4 for "Founth" Measure so on...

Sample Data table and Measure Pic are mentioned Below

[afzalphatan]

Can you make it more clear.. if you can share snap of SMALL sample data set and the expected result

[nataliag93]

Hi, afzalphatan

Sure!  The dataset is like the one below.

What I want to achieve in something like this table:

 WHERE: volume is not the just the volume contracted on that month but ALL the volume contracted in the dataset until that moment, e.g:

May-18: ALL the volume in the dataset UNTIL end of May-18;

Jun-18: ALL the volume in the dataset  UNTIL end of June-2018 and it goes on...

and 

When I say:

"Current" I mean month of delivery period is the same as the month of contract date (e.g: May-2018 contracted and May-2018 delivered)

"First" I mean month of delivery period is the next month of the contract date; (e.g May-2018 contracted and June-2018 delivered);

"Second" I mean month of delivery period is two months after the month of contract date; (e.g May-2018 contracted and July-2018 delivered)

Hope its clearer now!! thanks in advance! 🙂

[v-xjiin-msft]

Hi @nataliag93,

Not quite understand about the relationship between your shared dataset and your expected result.

Why Current for May-18 is 100 or First for May-18 is 125 and so on? Please share us some sample data and its related expected result. It'll help us a lot to understand your requirement.

Thanks,
Xi Jin.

[nataliag93]

Hi @v-xjiin-msft

I think with the following picture its easier to understand what Iam trying to do... the formulas in Excel shows which cell and calculation Im doing. do you think is there to this automatic calculation in PowerBI?

thanks!!