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

[Anonymous]

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 

[CNENFRNL]

@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]

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 · Accepted solution]

@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"

[Anonymous]

Thanks so much. Will try and out and revert. Thanks