Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Modelling data upto 3rd level based on Month

Hi, Another major Challenge, I have a requirement to calculate the YTD for certain business unit which has 2 below sub section and I need to select YTD for the BU and its sub section. For example ...
Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi Anonymous,

     

    You can use Split Column feature in Query Editor to get three columns from Organization Unit:

     

     

    Then create a calculated column to return month number: 

     

    MonthNum = SWITCH(TRUE(),'Table2'[Month]="JAN",1,'Table2'[Month]="FEB",2)

     

    Then create a measure:

    YTD = CALCULATE(SUM(Table2[Count]),FILTER(ALLSELECTED(Table2),'Table2'[MonthNum]<=MAX('Table2'[MonthNum])))

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi Anonymous,

 

You can use Split Column feature in Query Editor to get three columns from Organization Unit:

 

 

Then create a calculated column to return month number: 

 

MonthNum = SWITCH(TRUE(),'Table2'[Month]="JAN",1,'Table2'[Month]="FEB",2)

 

Then create a measure:

YTD = CALCULATE(SUM(Table2[Count]),FILTER(ALLSELECTED(Table2),'Table2'[MonthNum]<=MAX('Table2'[MonthNum])))

 

 

Best Regards,
Qiuyun Yu

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-qiuyu-msft,

    Thanks this work, But I have months for various year, so how to classify that like I get values based on year. Right now with this formulae, It adds the  data for Jan month for 2017 and 2018 both. I need to keep the year wise value seperate and calculate YTD for different year.

    Please help me on that.

    Thanks,
    ankku

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Icon for Community Support rankCommunity Support
      8 years ago

      Hi Anonymous,

       

      This should be a different requirement from the original post. I would suggest you post a new thread in Desktop forum. Our community support team members will help you. :smileyhappy:

       

      Best Regards,

      Qiuyun Yu