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.
I have 2 tables in PowerBI, "Calendar dim" and "Fact general ledger". I would like to filter the "Fact general ledger" table with a slicer which uses dates from "Calendar dim" table in a way that when i select in slicer 8.3.2015. from example below it filters my "Fact general ledger" so the result should be like in the table on the far right "Expected general ledger".
So the logic should be(in the example below):
How can i achieve that by using DAX and relationships? I want to use one date dim to filter fact table on 2 different columns each having it's own logic.
Solved! Go to Solution.
I'm according with @Anonymous, but i use this measure:
QtyFact =
VAR MinDate = MIN('CalendarDate'[Date])
VAR MaxDate = MAX('CalendarDate'[Date])
RETURN CALCULATE(SUM('Fact'[Amount]);INTERSECT(VALUES('Fact'[DocumentDate]);DATESBETWEEN('Fact'[DocumentDate]);MinDate;MaxDate)))
QtyExp =
VAR MinDate = MIN('CalendarDate'[Date])
VAR MaxDate = MAX('CalendarDate'[Date])
RETURN CALCULATE(SUM('Exp'[Amount]);INTERSECT(VALUES('Exp'[DocumentDate]);DATESBETWEEN('Exp'[DocumentDate]);MinDate;MaxDate)))
You can achieve this by creating a special MEASURE:
1. First, you DO NOT connect your calendar table to the Fact
2. Create a measure:
Special Sum = CALCULATE(SUM(Amount),Filter(Fact,YEAR(Fact[DocumentDate])=MAX(YEAR(CalendarTable[Date]))&&Fact[BookingDate]<=Max(CalendarTable[Date])))
Michael
Hi mshparber,
in the part MAX(YEAR(CalendarTable[Date])) i'm getting an error:
"The MAX function only accepts a column reference as an argument"
Any ideas?
Try YEAR(MAX(CalendarTable[Date]))
That works! Thanks
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |