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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
mshilendhari
Frequent Visitor

Date since last added in Power Query or DAX

Hi all,

 

I have a scenario where I need to show the date when a part became watchlist. Data is in the format shown below and this is filtered for one part. In this example, this part was initially on watchlist from 6/25 to 6/29 and then again it became watchlist from 8/9 till today. What I am looking for as date when last added is 8/9. However, since there is no other differentiating category, its always giving me 6/25 as the date when it was last added. Please help on how to achieve this use case. It would be great if you can help me with a solution using Power Query as I am showing a lot of columns in the dashboard in tabular format. So anything on DAX might end up using a lot of CPU/memory. Thanks much in advance!

screeenshot.png

3 REPLIES 3
edhans
Super User
Super User

@mshilendhari - see if this is what you want. If not, please provide data per links below and how the calculation should be arrived at. The below M code turns this:

edhans_0-1599666697899.png

into this. You can see the items in red retain the latest Watchlist date for the same item.

edhans_1-1599666732488.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLUN9Q3MjAyADLDE0uSM3Iyi0uUYnVgkkb4JI1hkp55xQWpySWZ+XlIsibYtToBRSxwWQqRxGEpRNIYn6QJDhdBZE2xycYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Item"}, 
            {
                {
                    "WatchListDate",
                    each
                        let
                            varStatus = "Watchlist",
                            varGroupTable = _
                        in
                    
                    Table.AddColumn(
                        _,
                        "Test",
                        each
                            let
                                varCurrentDate = [Date]
                            in
                            List.Max(
                                Table.SelectRows(
                                    varGroupTable,
                                    each [Status] = varStatus and [Date] <= varCurrentDate
                                )[Date]
                            )
                    )
                    
                }
            }
        ),
    #"Expanded WatchListDate" = Table.ExpandTableColumn(#"Grouped Rows", "WatchListDate", {"Date", "Status", "Test"}, {"Date", "Status", "Test"})
in
    #"Expanded WatchListDate"

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@mshilendhari - What is the logic to arrive at that date? The minimum date after the last >1 day gap? @ImkeF and @edhans might have a Power Query solution. Otherwise, would need to use something like MTBF to calculate a column with difference in day from previous and then another calculated column to flag the date. Calculated columns might not be so bad as this would be part of refresh.

 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@mshilendhari , continuous Streak problem.

I think is memory consuming. I have a blog that deals with different use case

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

 

see if this can help

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.