[ Doubt about how to have similar result on powerquery ]

[MarceloVieira]

Hello everyone, is there a way to have a similar result direct on powerquery?

FT_OVERDUE_24 = -- THIS IS A CALCULATED TABLE
FILTER(
    ALL( FT_OVERDUE ),
    VAR YEARMONTH_OVERDUE = FT_OVERDUE[YEARMONTH_SEQUENTIAL_OVER]
    RETURN
        FT_OVERDUE[YEARMONTH_SEQUENTIAL_OVER] = YEARMONTH_OVERDUE &&
        FT_OVERDUE[YEARMONTH_SEQUENTIAL_DUE] >= YEARMONTH_OVERDUE - 23 &&
        FT_OVERDUE[YEARMONTH_SEQUENTIAL_DUE] <= YEARMONTH_OVERDUE
)

This image is on aggregated level. The fact table is on a client granularity.

[Anonymous · Accepted solution]

Hi @MarceloVieira ,

Please follow these steps:

Based on your description, I created the data:

1.Create custom columns in the power query editor and write expressions:

if

[due] >= [over] - 23 and [due] <= [over]

then

"1"

else

"0"

2.Filter the new column, only retain the value of "1" data.

3.After getting the desired data, delete the newly created custom column.

4.If you want to group the YEARMONTH_SEQUENTIAL_OVER column, you can refer to the documentation: How to GROUP BY or summarize rows - Power Query | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarceloVieira]

Thks @Anonymous 

The solution works very well!!! Really thks for the help!!