Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
Solved

DAX calendar problem

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

 

  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You can simplify it like this:

     

     

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

     

     

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    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's avatar
      gmsamborn
      Super User
      2 years ago

      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.

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    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

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      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's avatar
    Mederic
    Post Patron
    2 years ago

    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