Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert Earlier function in DAX to Power Query (Reverse Cumulative)

Hello,

Apologies in advance but I cannot seem to find a solution to this question.

Basically, I wrote a DAX query that works perfectly, but i realised a Power query will be better because I need Power's pivot functionality for some required post analysis. I just started learning DAX and have no idea how to write power queries.

 

For the query, I created two variables _department & _project and execute the following:

CALCULATE(COUNTROWS('Table'),FILTER('Table',__department='Table'[department]),FILTER('Table',__project='Table'[Project type]),FILTER('Table','Table'[date]>=EARLIER('Table'[date]))
The query filters the table by department and project, then does a reverse cummulative count on the filtered results, grouped by dates. 
 
Any idea would be a good starting point for me.
Thanks 
 
Dashboard
DAX
DAX to Power M Query
EARLIER
Power M Query
Reverse Cumulative
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Anonymous , here's the M code for your reference,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0NFTSUfIozU3MUwhKLc4vLUpOLQaKGBkYGeoaAJGxUqwONdWZIKtzy8xLzEtOxWkOFnlTCuVNyHEnHnWm5IQPIXfROlzoEI8Q+5yMiDOHsDpjctThDGfc8qYE5IHmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Department = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Department", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Project", "Department"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

    #"Reverse Cumulative Count" = Table.AddColumn(#"Grouped Rows", "Reverse Cumulative Count", each List.Sum(Table.SelectRows(#"Grouped Rows", (x) => x[Project] = [Project] and x[Department] = [Department] and x[Date] >= [Date])[Count]))
//                               ^^^^^^^^^^^^^^^^^
//                             it's equivalent to EARLIER()
in
    #"Reverse Cumulative Count"

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous , as to your DAX formula alone, it could be simplified as

    CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[department] = __department
            && 'Table'[Project type] = __project
            && 'Table'[date] >= EARLIER ( 'Table'[date] )
    )
)

    as to the equivalence to EARLIER(), I think you can conduct some filtering after grouping the dataset by columns [department] and [Project type]. Paste some mockup data for further explanation if you want.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey, 

      I have been having some challenges replying. Made several attempts for over an hour. Something about 'Post flooding detected (community received posts of a unique message more than 1 times within 3,600'

      I hope this works!

       

      My response to your question 

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Anonymous , here's the M code for your reference,

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0NFTSUfIozU3MUwhKLc4vLUpOLQaKGBkYGeoaAJGxUqwONdWZIKtzy8xLzEtOxWkOFnlTCuVNyHEnHnWm5IQPIXfROlzoEI8Q+5yMiDOHsDpjctThDGfc8qYE5IHmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Department = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Department", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Project", "Department"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

    #"Reverse Cumulative Count" = Table.AddColumn(#"Grouped Rows", "Reverse Cumulative Count", each List.Sum(Table.SelectRows(#"Grouped Rows", (x) => x[Project] = [Project] and x[Department] = [Department] and x[Date] >= [Date])[Count]))
//                               ^^^^^^^^^^^^^^^^^
//                             it's equivalent to EARLIER()
in
    #"Reverse Cumulative Count"