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 whe...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Try YEAR(MAX(CalendarTable[Date])) 

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

    • skitzo's avatar
      skitzo
      Frequent Visitor
      9 years ago

      That works! Thanks