cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beekee
Helper IV
Helper IV

Cumulative measure based on TREATAS

Hi All,

 

I want to display cumulative line chart based on the Revenue. However, my issue is that the x-axis in the line chart comes from another table [Para_Period] which has no relationship with Fact_Sales. I'm using TREATAS function get the [Revenue] measure, however, I'm not able to use the same measure to derive the [Cumulative Revenue]. 

 

Does anyone know how to create cumulative measure in this case?

 

beekee_0-1619344741070.png

 

 

Thanks.

 

Regards,

BK

1 ACCEPTED SOLUTION

Hi, @beekee 

Thank you for sharing your sample pbix file.

Please correct me if I wrongly understood your data model, but I could not find a date-type column in ParaPeriod table and fact table. So I created ones based on the key column. I could not use key column in the measure, because it is text type, not the date-type.

 

Please check the below link, and all measures I created are inside the sample pbix file.

 

https://www.dropbox.com/s/94dnih8v6s7kihl/Test%20Cumulative.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
Jihwan_Kim
Super User
Super User

Hi, @beekee 

Please check the below picture and sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

 

Picture3.pngPicture4.png

 

https://www.dropbox.com/s/wwpl5389cjq42d0/beekeev3.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim in my Para Period table, there are duplicates in the week column (same week appear in two separate fiscal month). Therefore I'm not able to max the week index column to get the same output as your "currentperiodmaxdate"

Hi, @beekee 

Thank you for your feedback.

In my measure, I am using MAX for date column, not the week column.

If it is OK with you, please share your sample pbix file, then I can try to look into it to come up with a more accurate measure.

 

Thanks.

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim , 

 

I have created a sample PBIX. Currently the measure is not working as expected in the sample file, not sure why. Instead of showing 267 for the period, it should be displaying 12, 22, and 31 (refer to table on the right). Note that I have a reporting week slicer to filter the left table to decide what's the end date. Also, I will eventually convert the table to line graph instead. Thanks!

 

https://www.dropbox.com/s/ojof7opjuf92itd/Test%20Cumulative.pbix?dl=0 

Hi, @beekee 

Thank you for sharing your sample pbix file.

Please correct me if I wrongly understood your data model, but I could not find a date-type column in ParaPeriod table and fact table. So I created ones based on the key column. I could not use key column in the measure, because it is text type, not the date-type.

 

Please check the below link, and all measures I created are inside the sample pbix file.

 

https://www.dropbox.com/s/94dnih8v6s7kihl/Test%20Cumulative.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim , I have updated the [Actl_Rev_Range] measure in the PBIX. Currently the measure you have created doesn't factor in weeks that doesn't belong in the quarter selected in slicer, for example, 02-Apr (Wk40) should be showing 11 instead of 12. 

 

Is it possible to make use of [Actl_Rev_Final (format)] in your logic as I'm using this measure in my dashboard. Basically this measure will only display weeks that belong to the fiscal quarter and assign the correct value accordingly.

 

https://www.dropbox.com/s/ojof7opjuf92itd/Test%20Cumulative.pbix?dl=0 

Hi @Jihwan_Kim , please ignore my previous reply. I do realise I can just make use of the measure you have created. Thank you so much for the help!

 

Regards,

BK

Fowmy
Super User
Super User

@beekee 

Can you share the fact and the period table to understand the model?




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!