Cumulative measure based on TREATAS

[Anonymous]

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?

Thanks.

Regards,

BK

[Jihwan_Kim]

Hi, @Anonymous 

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.

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

[Anonymous]

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"

[Jihwan_Kim]

Hi, @Anonymous 

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

[Anonymous]

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 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

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

[Anonymous]

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 

[Anonymous]

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]

@Anonymous 

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