Forum Discussion

skitzo's avatar
skitzo
Frequent Visitor
9 years ago
Solved

Solving a date puzzle!

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Try YEAR(MAX(CalendarTable[Date])) 

5 Replies

  • FilippoV's avatar
    FilippoV
    Helper I
    9 years ago

    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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      skitzo
      Frequent Visitor
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        Try YEAR(MAX(CalendarTable[Date]))