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

[Moushmi]

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 prior years based on date selected in date filter), Current year balance should be expanded with company codes, hierarchy with department code. (Pasted below... I hidded the names but i need to show like this)

Problem is when I select any date from previous year.. I am only seeing beginning balance column. I think my calculated column is not capturing the Post Date from filter. 

Year Category =

VAR SelectedYear = YEAR(MAX('GL'[Post_Date]))

RETURN

    SWITCH(

        TRUE(),

        YEAR(GL[Post_Date]) = SelectedYear, 'GL'[Company_Code] &" - "& 'GL'[Company_Name],

        YEAR(GL[Post_Date]) < SelectedYear, "Beginning balance",

        BLANK()

    )

can you tell me how can I achive showing beginning balance and current balance based on user date selection?

[Moushmi]

Hey, by this method I can only show previous year and current year. I want to show beginning balance which will have data for years prior to current year. for eg. if selected date in slicer is 06/30/2025 my beginning balance should show sum of amount for years 2024, 2023, 2022,....

Also, I don't want my current year to just be current year in the column lable. It needs to be company codes in the column to display current year balance.

[amitchandak · Accepted solution]

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

[v-menakakota]

Hi @Moushmi  ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @amitchandak  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @Moushmi ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.

[v-menakakota]

Hi @Moushmi ,

Can you please confirm whether the issue got resolved. If solved can you you please share your solution here,so that other community members can find it easily.

Thank you.

[vojtechsima]

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