Forum Discussion

saket_mca1's avatar
saket_mca1
Frequent Visitor
9 years ago
Solved

Calculate Measure

Hi,   I have a scenairo in which I have to display 2(Sales, Sales_History) measure values by Region having 'Month-Year' as a filter. Consider like I have data for each day from Jan 2016 to July 201...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    saket_mca1,

     

    This requirement can be done by a cumulative total. Please refer to the steps below to see the details.

    1. Create two calculate column in your date table.
      YearMonth = FORMAT(RegionSales[Date],"YYYY-MMM")
      YearMonth2 = FORMAT(RegionSales[Date],"YYYYMM")
    2. Create cumulative total measure
      Sales_History = CALCULATE(SUM(RegionSales[Sales]),FILTER(ALLEXCEPT(RegionSales,RegionSales[Region]),RegionSales[YearMonth2]<=MAX(RegionSales[YearMonth2])))

    Regards,

    Charlie Liao

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

saket_mca1,

 

This requirement can be done by a cumulative total. Please refer to the steps below to see the details.

  1. Create two calculate column in your date table.
    YearMonth = FORMAT(RegionSales[Date],"YYYY-MMM")
    YearMonth2 = FORMAT(RegionSales[Date],"YYYYMM")
  2. Create cumulative total measure
    Sales_History = CALCULATE(SUM(RegionSales[Sales]),FILTER(ALLEXCEPT(RegionSales,RegionSales[Region]),RegionSales[YearMonth2]<=MAX(RegionSales[YearMonth2])))

Regards,

Charlie Liao

  • saket_mca1's avatar
    saket_mca1
    Frequent Visitor
    8 years ago

    Hi Charlie,

     

    Thanks for your previous response. It helped me a lot. 

    In continuous to our discussion, I have the same requirement but with some change. In previous measure calculation we applied filter based on Region. But now I am looking for a calculated measure which don't have any filter.

     

    So that we can display the measure column not only with Region but also with other dimension column e.g. Product Category, Sales Type. 

     

    I tried doing that but didn't figure it out. So Please let me know if you can help me out with this requirement.

     

    Regards

    Saket