Power Query - Custom Col - Based on rows with Today and the past 7 days

[Russ_EMC]

Hi All

Please I need some help to create a custom column in Power query to show events based on rows with Today and the past 7 days. 

The main criteria I wish to have is to base on identify the product with today's date and also appearing in the past 7 days. In the image below, Product 1 meets these requirement and Product 2 & 3 do not as it does not have any rows with today's date. 

Please see example of what I intend to do below. I would really appreciate any help I can get. Thank you all for reading this and I look forward to any advice you might have for me.

The current work around is I copy today's products onto to a different table then have the formulas shown below pointing to that table. 

Helper Column - D

=IF(ISNA(VLOOKUP([@[Product ID]],TodayProducts[Product ID],1,FALSE)),1,COUNTIF(C:C,C2))

The formula in the Helper column is a logical test check if the product ID in Column C is also on the Today's table, if this product ID is not found then false returns 1, However if true then the formula counts all the product IDs in Col C. 

Col E 

=IF([@Date]=TODAY(),"Yes",IF([@[Helper Column]]>1,"Yes","No"))

Combines those product ID's with today's date and those with greater than 1 in the helper column, these are the products we are interested in on our report. 

Please could you advise if this possible in Power query or any other formulas which can be added to the custom column so I do not have to manually add today's products in a separate table every day.

[Russ_EMC]

The current work around is I copy today's products onto to a different table then have the formulas shown below pointing to that table. 

Helper Column - D

=IF(ISNA(VLOOKUP([@[Product ID]],TodayProducts[Product ID],1,FALSE)),1,COUNTIF(C:C,C2))

The formula in the Helper column is a logical test check if the product ID in Column C is also on the Today's table, if this product ID is not found then false returns 1, However if true then the formula counts all the product IDs in Col C. 

Col E 

=IF([@Date]=TODAY(),"Yes",IF([@[Helper Column]]>1,"Yes","No"))

Combines those product ID's with today's date and those with greater than 1 in the helper column, these are the products we are interested in on our report. 

Please could you advise if this possible in Power query or any other formulas which can be added to the custom column so I do not have to manually add today's products in a separate table every day.

[Vvelarde]

@Russ_EMC

Hi, in DAX you can try this:

Calculated Columns

In7days =
IF ( TODAY () - Table1[Date] <= 7; "in 7 days"; "no in 7 days" )

InToday =
IF ( Table1[Date] = TODAY (); "Today" )

Include =
IF (
    CALCULATE (
        COUNT ( Table1[ProductID] );
        FILTER ( ALLEXCEPT ( Table1; Table1[Product] ); Table1[InToday] = "Today" )
    )
        > 0
        && Table1[In7days] = "in 7 days";
    "Yes";
    "No"
)

Filter Incluye = Yes.

Regards

Victor

[Anonymous]

Hi @Russ_EMC,

You could add a custom column in Power Query with the code as follows

= if Date.AddDays(DateTime.Date(DateTime.LocalNow()),-7) > [Date] then "False" else "True"

This code evaluates whether the date in the Date column is greater than the date it was 8 days ago it will return "True" else it will return "False". You can play around with this as you will.

Let me know if this helps.

Best,

Kris

[MattAllington]

This could work, but I suspect there is a better approach. Why not load all the data and then use a relative date slicer in Power Bi to show all the products with a record in the last 7 days?

[Russ_EMC]

Hi Matt, 

Thanks for the suggestion, unfortunately when I tried this it only offers me products with today's date or in the past 7 days. The relative date slicer is unable to highlight products that have both today and in the past 7 days.

[MattAllington]

OK, so a simple solution would be to add a new column into the table you load that indicates if the date is in the last 7 days or not.  You can do this as a calculated column in DAX or as a custom column in Power Query.

As a calculated column, =if(Today()-7 >=Table[Date],"Include","Exclude")

Just filter on this column