cancel
Showing results for 
Search instead for 
Did you mean: 
0

GENERATE/variables bug

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

Status: Delivered
Comments
v-jiascu-msft
Microsoft

Hi @Daniil,

 

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

 

Best Regards,

Dale

Vicky_Song
Impactful Individual
Status changed to: Accepted
 
v-jiascu-msft
Microsoft

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
Microsoft

Hi,

 

This issue will be fixed in the release October 2018.

 

Best Regards,

Dale

v-jiascu-msft
Microsoft

Hi,

 

This issue is fixed in the release November 2018.

 

Best Regards,

Dale

v-jiascu-msft
Microsoft
Status changed to: Delivered