Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nataliag93
Frequent Visitor

Cumulative for certain period with filters applied per period

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.

dataset.PNG

 

 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.

 

Example.PNG

 

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

 

 THANKS IN ADVANCE!!!

5 REPLIES 5
afzalphatan
Resolver I
Resolver I

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 BelowSol.PNG

Current.PNGFirst.PNGData Table.PNG

 

 

afzalphatan
Resolver I
Resolver I

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

Hi, afzalphatan

 

Sure!  The dataset is like the one below.dataset.PNG

 

 

What I want to achieve in something like this table:

example2.PNG

 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! 🙂

 

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.

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!!

Example dataset.PNG

 

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.