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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.