Solving a date puzzle!

[skitzo]

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):

1. when i select 8.3.2015, filter the fact to take all the records where "Document date" between 1.1.2015. and 31.12.2015.
2. and  "Booking date" <=8.3.2015

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.

[FilippoV]

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)))

[Anonymous]

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

[skitzo]

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?

[Anonymous · Accepted solution]

Try YEAR(MAX(CalendarTable[Date])) 

[skitzo]

That works! Thanks