Forum Discussion

Moushmi's avatar
Moushmi
Frequent Visitor
1 year ago
Solved

Getting max date from date filter using DAX measure but ignoring in matrix calculated column

  Hi, I am using Direct Query I am trying to create a Matrix to display financial report. My matrix should show, Row: Account code Value: Amount Column: Beginning balance ( balance of pri...
Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    Moushmi , You need build balances using cumulative method. If the day is driver then you can have like 

     

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[date] <min(date[date]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[date] <min(date[date])))

     

    same way I can use Year of FY (Numeric)

    BOP= CALCULATE(SUM(Table[IN]),filter(all(date),date[Year] <min(date[Year]))) -
    CALCULATE(SUM(Table[OUT]),filter(all(date),date[Year] <min(date[Year])))

     

    Same way End of period is 

     

    EOP= CALCULATE(SUM(Table[In]),filter(all(date),date[date] <=Max(date[date]))) -
    CALCULATE(SUM(Table[Out]),filter(all(date),date[date] <= Max(date[date])))

vojtechsima's avatar
vojtechsima
Super User
1 year ago

Hi, Moushmi ,
well, i would scratch your calculated column as it's column and static, so it can'T really work dynamically in report upon selection.

 

And just use this (as a measure) for current year and previous year.

 

YTD = TOTALYTD ( [measure], 'calendar'[Date] )

YTD PY =
CALCULATE (
    [YTD],
    SAMEPERIODLASTYEAR ( 'calendar'[Date] )
)