DAX calendar problem

[Mederic]

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

[Mederic]

@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

 

[Mederic]

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

[gmsamborn]

Sorry.  You're correct.

[lbendlin · Accepted solution]

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.

[Mederic]

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

[gmsamborn]

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.

[lbendlin]

You can simplify it like this:

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