Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Russ_EMC
Regular Visitor

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

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.

 

 

Untitled.jpg

6 REPLIES 6
Russ_EMC
Regular Visitor

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.

 

Untitled.jpg

Vvelarde
Community Champion
Community Champion

@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




Lima - Peru
Anonymous
Not applicable

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

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?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.