Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
saket_mca1
Frequent Visitor

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 2017 and I applied filter as Mar-2017. then valuse for measures should be as below

 

Sales:- Should display data for Mar-2017 month only.

Sales_History:- Should display data from Jan 2016 to Mar 2017.

 

For e.g

                              Sales                 Sales_History

--------------------------------------------------------

Region1                 20                          235 (Sum from Jan 2016 to Mar 2017 for Region1) 

Region2                 25                          225 (Sum from Jan 2016 to Mar 2017 for Region2)

Region1                 15                          215 (Sum from Jan 2016 to Mar 2017 for Region3)

Region1                 22                          205 (Sum from Jan 2016 to Mar 2017 for Region4)

 

Please anyone let me know how this can be done in Power BI.

 

Regards

Saket 

 

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@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")
    Capture.PNG
  2. Create cumulative total measure
    Sales_History = CALCULATE(SUM(RegionSales[Sales]),FILTER(ALLEXCEPT(RegionSales,RegionSales[Region]),RegionSales[YearMonth2]<=MAX(RegionSales[YearMonth2])))
    Capture1.PNGCapture2.PNG

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
v-caliao-msft
Employee
Employee

@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")
    Capture.PNG
  2. Create cumulative total measure
    Sales_History = CALCULATE(SUM(RegionSales[Sales]),FILTER(ALLEXCEPT(RegionSales,RegionSales[Region]),RegionSales[YearMonth2]<=MAX(RegionSales[YearMonth2])))
    Capture1.PNGCapture2.PNG

Regards,

Charlie Liao

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

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors