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.
Hello,
I've been searching high and low for a solution for this and I still can't find a way to make it work.
I have a table that contains sales values, the columns are as follow:
Sales Person
Date
Sales Amount
and I have a slicer with dates that are coming from a date table.
My goal is to create a measure that sums up ALL the sales amount until the max selected date. so for example, if the user selected the dates from the slicer as below:
The measure would sum up all the sales amount from 2020 until the max selected month in 2023
Any support would be appreciated, and I am happy to answer any quesitons to clarify the issue.
Yes, but you will have to add a dates table to your model. Then you use the dates table in your slicer and you can write the measure like this.
Total To Date =
VAR _Max = MAX ( DATES[Date] )
RETURN
CALCULATE ( SUM ( YourTable[Amount] ), DATES[Date] <= _Max )
I am assuming you are going to want to have a filter on sales person still work so try a filter like this.
Total To Date =
VAR _Max = MAX ( YourTable[Date] )
RETURN
CALCULATE ( SUM ( YourTable[Amount] ), ALLEXCEPT ( YourTable, YourTable[Sales Person] ), YourTable[Date] <= _Max )
is there any other way of doing this? using ALLEXFCEPT prevents me from adding data from other tables to the visuals.
the visual I'm using is a matrix, it pulls two rows from dimensions tables that are connect to all the other tables, the values in the matrix are the measures and one of the measures is the sales amount. If I use ALLEXCEPT, I can't use data from other tables in the visual.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |