Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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?

 

 

 

Thanks.

 

Regards,

BK

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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

     

8 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

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




  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        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