Forum Discussion

powerbi_Mandal's avatar
powerbi_Mandal
Frequent Visitor
2 years ago

Not able to force date format in DAX formula

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

 

date format
variables

3 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      powerbi_Mandal
      Frequent Visitor
      2 years ago

      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. 

  • powerbi_Mandal's avatar
    powerbi_Mandal
    Frequent Visitor
    2 years ago

    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*

    🙂