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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Peppearson
Helper I
Helper I

Combining Two Measures

Hi All,

 

I'm trying to create a measure which is a combination of two different measures and which changes in line with a date slicer.

 

Example

 

There are two data tables with actual sales and projected sales covering the same time period.

Peppearson_1-1669043636181.png

 

So when the date filter is set to March 22 the expected outcome is below:

 

Actual Sales for dates before or equal to the filter and projected sales for dates coming after the date filter

Peppearson_0-1669043466195.png

 

In power bi so far I have two separate measures Sum(Actual Sales) and Sum(Projected Sales).

 

However not sure how I would go about combining them based off the datefilter.

 

Any ideas would be much appreciated.

 

Kind regards,

 

1 ACCEPTED SOLUTION
Shaurya
Memorable Member
Memorable Member

Hi @Peppearson,

 

You can try using:

 

Combined = CALCULATE(SUM('Table1'[Actual Sales]), FILTER('Table1', MONTH('Table1'[Date])<=MONTH(SELECTEDVALUE('Filter'[Date])))) + CALCULATE(SUM('Table2'[Projected Sales]), FILTER('Table2', MONTH('Table2'[Date])>MONTH(SELECTEDVALUE('Filter'[Date]))))

 

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

 

 

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @Peppearson ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _selecteddate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _1jie =
    CALCULATE (
        MAX ( Actual[Actual Sales] ),
        FILTER (
            ALL ( Actual ),
            Actual[Date] = SELECTEDVALUE ( Actual[Date] )
                && Actual[Food Type] = SELECTEDVALUE ( Actual[Food Type] )
        )
    )
VAR _2jie =
    CALCULATE (
        MAX ( Projected[Projected Sales] ),
        FILTER (
            ALL ( Projected ),
            Projected[Date] = SELECTEDVALUE ( Actual[Date] )
                && Projected[Food Type] = SELECTEDVALUE ( Actual[Food Type] )
        )
    )
VAR _selectedfood =
    SELECTEDVALUE ( food[Food Type] )
RETURN
    IF (
        _selecteddate >= SELECTEDVALUE ( Actual[Date] )
            && _selectedfood = SELECTEDVALUE ( Actual[Food Type] ),
        _1jie,
        _2jie
    )

vpollymsft_0-1669098147551.png

If I hvae misunderstood your meaning, please provide  desired output with more details.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Shaurya
Memorable Member
Memorable Member

Hi @Peppearson,

 

You can try using:

 

Combined = CALCULATE(SUM('Table1'[Actual Sales]), FILTER('Table1', MONTH('Table1'[Date])<=MONTH(SELECTEDVALUE('Filter'[Date])))) + CALCULATE(SUM('Table2'[Projected Sales]), FILTER('Table2', MONTH('Table2'[Date])>MONTH(SELECTEDVALUE('Filter'[Date]))))

 

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

 

 

Hi @Shaurya 
Thank you for your help.
I used a variation on your dax code but turned both calculate formulas into variables.
I also replaced the below in both formulas with the parameter value that I'm using.

MONTH(SELECTEDVALUE('Filter'[Date])

See below what the code ended up like.

Combined =


Var Actual Sales 
CALCULATE(SUM('Table1'[Actual Sales]), FILTER('Table1', MONTH('Table1'[Date])<=Parameter[Parameter Value]))

VAR ProjectedSales
CALCULATE(SUM('Table2'[Projected Sales]), FILTER('Table2', MONTH('Table2'[Date])>Parameter[Parameter Value]))

Return 
If(ActualSales=0,
ProjectedSales(measure),
ActualSales+ProjectedSales

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors