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.
Hi all!
Here's my problem: I have two tables, and each of them have a Date and a Unit column. I've created a date table and successfully set up a relationship between the two tables through their "Date" column, but not through their "Unit" column (multi-multi, both have the same distinct values).
Right now I need to create a report filtering (with slicers) by both Date (which works) and Unit (which doesn't) and show the results of measures which use both columns as parameters. For example, here's one of them (calculates last year sales based on current date slicer -- when selecting feb 2019, it should show total sales of feb 2018):
SalesTotal_LY = CALCULATE(SUM(Table1[MonthlySales]),SAMEPERIODLASTYEAR(Calendar[MonthYear]),ALLEXCEPT(Table1,Table1[Unit],Table1[MonthYear]),Table1[ProductID]="001")
This measure results in blank (as do the others). What am I doing wrong?
For reference, my Units slicer comes from Table2 (measures referencing only Table2[Unit] and Calendar[MonthYear] seem to work just fine) and my Date slicer comes from Calendar[MonthYear].
Solved! Go to Solution.
I have! I might have figured it out.
I decided not to use SAMEPERIODLASTYEAR (though I'm not sure it has any part in making it work) and instead ended up with this measure:
SalesTotal_LY =
VAR LastYr = DATEADD(Calendar[MonthYear],-1,YEAR) RETURN CALCULATE(
SUM(Table1[Sales]),
USERELATIONSHIP(Table1[Units],Table2[Units]),
Table1[MonthYear]=LastYr,
FILTER(ALLEXCEPT(Table1,Table1[MonthYear]),
Table1[Units]=[RelUnit]),
Table1[ItemId]="001")
[RelUnit] looks like this (I'm not even sure I need it, but haven't tried taking it out yet:
RelUnit = CALCULATE(MIN(Table1[Units]),USERELATIONSHIP(Table1[Units],Table2[Units]))
I have! I might have figured it out.
I decided not to use SAMEPERIODLASTYEAR (though I'm not sure it has any part in making it work) and instead ended up with this measure:
SalesTotal_LY =
VAR LastYr = DATEADD(Calendar[MonthYear],-1,YEAR) RETURN CALCULATE(
SUM(Table1[Sales]),
USERELATIONSHIP(Table1[Units],Table2[Units]),
Table1[MonthYear]=LastYr,
FILTER(ALLEXCEPT(Table1,Table1[MonthYear]),
Table1[Units]=[RelUnit]),
Table1[ItemId]="001")
[RelUnit] looks like this (I'm not even sure I need it, but haven't tried taking it out yet:
RelUnit = CALCULATE(MIN(Table1[Units]),USERELATIONSHIP(Table1[Units],Table2[Units]))
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 |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |