Not able to force date format in DAX formula

[powerbi_Mandal]

Hi,

I struggle with using date inside a formula. I have tried many different options, none is working. 

Here is my current formula, somehow it seems like I cannot pull the month and date out from the "Max(date)"

The only thing that works is when I hard code the date like this Date(2023, 10, 31), as you can see in my formula I try to add a variable, it does not work. However, this variable works if I write it in a Measure....

The table Calendar is an island, not connected to any other table. And I use a date-slicer for defining what date it is.

I have tried to format all dates, both in the Query data loading, and in front view. 

I have tried using Month(Max(Date)) etc, I have tried Mid(Max(Date),4,2), or Left(Max(Date),2) etc.... 
I have tried to add new column in my table, new measure...

What is the reason for this struggle with date and date format?

Best regards
E

[powerbi_Mandal]

I can still not figure out how to send the value of selcted "Date" to my column for calculation when the tables are not connected.

However it seems that the year-value is picked up as the value I get in return is the value for Max(Date) for the whole year...

debt by lenders = CALCULATE(SUMX(factSecurity, factSecurity[Loan Balance Start]), FILTER(factSecurity, factSecurity[Dates] = Max('Calendar'[Date]))) * CALCULATE(AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share]))

I've tried to parse the date-value as measure and then as variable... Still getting 31.12.2023 whatever I select

debt by lenders = VAR selYear =Right(Max('Calendar'[Date]),4) 
VAR selMonth = Left(Max('Calendar'[Date]),2)
VAR selDay = Mid(Max('Calendar'[Date]), 4,2)

Return

CALCULATE(SUMX(factSecurity, factSecurity[Loan Balance Start]), factSecurity[Dates] = Date(selYear, selMonth, selDay)) * CALCULATE(AVERAGEX(dimBankAndGuarantor, dimBankAndGuarantor[Share]))

*HELP*

🙂

[some_bih]

Hi @powerbi_Mandal The table Calendar is an island, not connected to any other table - why not connect it?

If two date columns, Calendar[Date] and factSecurity[Date] have the same data type it should work (together with relationship).

[powerbi_Mandal]

Well, this dasboard is covering many other topics where a date island seems to be my best option, i would rather not rebuild the whole model at this moment.