GENERATE/variables bug

[Daniil]

The bug can be reproduced in a blank Power BI Desktop file in the following way:

1. Create a Date table with the following code:

   Date = 
   ADDCOLUMNS (
       CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 12, 31 ) ),
       "End of Month", EOMONTH ( [Date], 0 )
   )

2. Mark the Date table as date table
3. Create the following table:

   NoRows = 
   GENERATE (
       CALCULATETABLE (
           VALUES ( 'Date'[End of Month] ),
           'Date'[End of Month] = DATE ( 2018, 4, 30 )
       ),
       VAR RunningMonths =
           CALCULATETABLE (
               VALUES ( 'Date'[End of Month] ),
               DATESINPERIOD ( 'Date'[Date], 'Date'[End of Month], -12, MONTH )
           )
       RETURN
           SELECTCOLUMNS ( RunningMonths, "Previous Months", 'Date'[End of Month] )
   )

4. Note how there are no rows, even though there should be twelve rows. Now try this (note the difference in red😞

   TwelveRows = 
   GENERATE (
       CALCULATETABLE (
           VALUES ( 'Date'[End of Month] ),
           'Date'[End of Month] = DATE ( 2018, 4, 30 )
       ),
       VAR RunningMonths =
           CALCULATETABLE (
               VALUES ( 'Date'[End of Month] ),
               DATESINPERIOD ( 'Date'[Date], 'Date'[End of Month], -12, MONTH )
           )
       RETURN
           SELECTCOLUMNS ( RunningMonths, "Previous Months", 'Date'[End of Month] + 0 )
   )

   The only difference is "+ 0" in SELECTCOLUMNS inside GENERATE. Now there are twelve rows.
5. Another way to get 12 rows is to avoid using a variable:

   TwelveRows2 =
   GENERATE (
       CALCULATETABLE (
           VALUES ( 'Date'[End of Month] ),
           'Date'[End of Month] = DATE ( 2018, 4, 30 )
       ),
       SELECTCOLUMNS (
           CALCULATETABLE (
               VALUES ( 'Date'[End of Month] ),
               DATESINPERIOD ( 'Date'[Date], 'Date'[End of Month], -12, MONTH )
           ),
           "Previous Months", 'Date'[End of Month]
       )
   )

Is there any explanation for this, or is this indeed a bug?

I am using Power BI Desktop 2.56.5023.1021 64-bit (March 2018)

Just in case, here is my file: https://1drv.ms/u/s!Arstm99Oom00gdxdpEIT0K7fWgz8fA

[v-jiascu-msft]

Hi @Daniil,

I have reported this issue to the Product Team: CRI 64579879.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Daniil,

This issue will be fixed in the May 2018 release. The workaround could be as follows.

>>>change the RETURN statement to SELECTCOLUMNS ( RunningMonths, "Previous Months", 'Date'[End of Month], "UnusedColumn", BLANK() )

Best Regards,

Dale

[v-jiascu-msft]

Hi,

This issue will be fixed in the release October 2018.

Best Regards,

Dale

[v-jiascu-msft]

Hi,

This issue is fixed in the release November 2018.

Best Regards,

Dale