topic Re: DAX calendar problem in DAX Commands and Tips

DAX calendar problem

Mederic — Sun, 25 Feb 2024 17:01:40 GMT

Hello,
I haven't used DAX for a while,
I came across the link below which I would like to apply personally.
However, I don't know this language at all and can't create the calendar despite the automatic translation.
The DAX code I used is the one in the link, but it doesn't work for me in Excel 2019.
Could you please help me with this 1st code ?

Thanks in advance

Best Regards

= GENERATE ( CALENDAR ( DATE ( 2020; 1; 1 ); DATE ( 2022; 12; 31 ) ); VAR currentDay = [Date] VAR year = YEAR ( currentDay ) VAR month = FORMAT ( currentDay; "MM" ) RETURN ROW ( "Calendar", year&month ) )

ID Start End Capital Depreciation 1001 01/01/2021 31/12/2021 30 000,00 2 500,00 1002 29/01/2021 29/01/2022 20 000,00 1 666,67 1003 01/12/2020 30/11/2021 150 000,00 12 500,00 1004 28/04/2021 27/04/2022 98 000,00 8 166,67

Re: DAX calendar problem

lbendlin — Sun, 25 Feb 2024 20:29:40 GMT

You can simplify it like this:

= ADDCOLUMNS ( CALENDAR ( "2020-01-01" ; "2022-12-31" ); "Calendar", FORMAT([Date]; "yyyyMM") )

Re: DAX calendar problem

Mederic — Mon, 26 Feb 2024 19:38:17 GMT

Hello lbendlin ,

Thank you for your message,
Unfortunately, it didn't work for this example,
I don't know whether the DAX formula instruction in the link is intended to create a new table of dates or to add a new column to the existing table.
I can't get the DAX formulas to work.
Maybe I'm doing it wrong!

Thanks in advance

Best Regards

Re: DAX calendar problem

gmsamborn — Mon, 26 Feb 2024 19:52:01 GMT

Hi Mederic lbendlin

I don't know anything about Power Query in Excel but it appears that you are trying to create a table in the window meant for creating a measure.

I wish I could help more but I'm unsure of how to create a table.

Re: DAX calendar problem

Mederic — Mon, 26 Feb 2024 20:06:20 GMT

Hello gmsamborn ,

Thanks for the information,
I also thought that the subject of the link was to create a new table,
Otherwise, I think you meant to say Dax instead of Power query

Best Regards

Re: DAX calendar problem

lbendlin — Mon, 26 Feb 2024 20:14:06 GMT

What I haven't figured out yet is how to create a table in Excel that would then accept a DAX query. I know you can bend the query of tables that are tied to existing connections, but I think you want to do this from scratch.

gmsamborn good observation about the measure thing.

Re: DAX calendar problem

gmsamborn — Mon, 26 Feb 2024 20:16:11 GMT

Sorry. You're correct.

Re: DAX calendar problem

Mederic — Mon, 26 Feb 2024 21:13:32 GMT

lbendlin , gmsamborn ,

I think I understand the problem better,
Excel and Power BI don't work the same way for creating tables.

Indeed lbendlin , you are right, we must to do this from scratch in Excel.

I used the code from lbendlin in Power BI and it works very well (see

Table1)

The one in the link, which is more complex, gives the same result (see Table2)
I'll see how I can create a new table in Excel

Best regards