FROM DAX TO CODE M POWER BI

[Aydeedglz]

Hi,

I am trying to move a formula from DAX to Trasnform data, but I am only getting errors, can anyone help me to convert it? This is my formula. What is the way to represent this in Query Editor? I don't have that much because is always an error, thanks

Original formula 

TOP_CurrentFuturePaid = IF(DAY(TODAY())<=SEM_TRX_ALL[ClosingWd],

    BLANK(),

    CALCULATE(

        SUM(SEM_TRX_ALL[Group_Currency_Amount__c]),

        SEM_TRX_ALL[akritiv__Status__c]<>"Open",OR(SEM_TRX_ALL[a_RevisedDocumentCategory]="Invoice",SEM_TRX_ALL[a_RevisedDocumentCategory]="Credit note"),

        FILTER(SEM_TRX_ALL,SEM_TRX_ALL[a_UltimateDue]>TODAY()&&SEM_TRX_ALL[a_UltimateDue]<=EOMONTH(TODAY(),0))))

Power Query

IF([Today Date Format]<=[ClosingWd],"",............

[Syk]

I asked ChatGPT your question exactly.. I'm curious if it will work for you!

let
    today = DateTime.LocalNow(),
    closingWd = SEM_TRX_ALL[ClosingWd],
    isTodayBeforeClosingWd = Date.Day(today) <= closingWd,
    filteredTable = Table.SelectRows(
        SEM_TRX_ALL,
        each [akritiv__Status__c] <> "Open" and (
            [a_RevisedDocumentCategory] = "Invoice" or [a_RevisedDocumentCategory] = "Credit note"
        ) and [a_UltimateDue] > today and [a_UltimateDue] <= Date.EndOfMonth(today)
    ),
    topCurrentFuturePaid = if isTodayBeforeClosingWd then null else List.Sum(filteredTable[Group_Currency_Amount__c])
in
    topCurrentFuturePaid

The M query language is a functional language that operates on data in tables. In this code, we define some variables at the beginning (today and closingWd) and then use them in subsequent steps to filter and transform the SEM_TRX_ALL table. We use Table.SelectRows to filter the table and then use List.Sum to calculate the sum of the Group_Currency_Amount__c column.

Note that the M query code assumes that SEM_TRX_ALL is a table in the current Power Query environment. You may need to adjust the code to match the name and schema of your actual data source.

[Aydeedglz]

Hi, thanks for the help! But is not working