Power Automate Monthly/Most Recent Sales Data in Email

[powerbinovice01]

Hi,
I have created a process flow in Power Automate. I am running a query against my data set, I developed the dax formula from the preformance analyzer. 
It send out the email report fine. I am looking to see if it is possible to filter to show only the most recent data. My most recent monthly reports are a few months behind. 
I have attempted to Initialize Variables, but it pulls Weekly and Monthly data. I have also tried to write a filter array and compose action expressions, with no luck. 
I would love to know some tips and tricks to produce a power automate email from a table I created in Power BI to show only the most recent montly sales growth %. 

Here is the expression I have used with Compse: 

concat(formatDateTime(startOfMonth(utcNow()), 'yyyy-MM-ddTHH:mm:ssZ'), '-', formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:ssZ'))

Filter array expression: 

where(formatDateTime(item()?['DateColumn'], 'yyyy-MM') eq formatDateTime(addDays(utcNow(), -30), 'yyyy-MM'))

Both tell me my expression is wrong.

Below this shows I have attempted to use the Initialize Variable, which produces a "more accurate" table, but include weekly and monthly data, not soley Monthly Sales information.

Here is the view of the email which highlights both September (weekly info) and August (Monthly info)

Any tips and tricks can help.

Thanks Power BI Guru! 

[edhans]

You want to filter your data in the DAX query. Paste that query from the viz into DAX Studio, clean it up so it is the core SUMMARIZECOLUMNS() query. 

You can obtain the MAX (latest) date from that query using MAXX(the query, [the date field]) and assign that to a variable, then wrap the entire SUMMARIZECOLUMNS() in a CALCULATETABLE() filtering for that date. You can also use TREATAS() inthe SUMMARIZECOLUMNS() filter conditions, but there will be a neglible performance diff.

You could post your DAX query here for assistance, or you need to head to the Power Automate forum if you want to filter your file using PA, but that will return a larger dataset to be filtered.

As an example, from DAX.DO - this is a query on sales but only for the last Due Date in the model.

EVALUATE
VAR varMaxDate =
    MAX ( sales[Due Date] )
RETURN
    SUMMARIZECOLUMNS (
        Customer[Customer Type],
        'Product'[Brand],
        Sales[Due Date],
        TREATAS ( { varMaxDate }, Sales[Due Date] ),
        "@Sales", [Sales Amount]
    )

You can play with that query here. https://dax.do/TRQwyrLLqlKNGx/

The TREATAS line is taking that max date and filtering the entire table. You can use a range of dates too.

EVALUATE
VAR varMaxDates =
    TOPN(10, VALUES(Sales[Due Date]), Sales[Due Date], DESC)
RETURN
    SUMMARIZECOLUMNS (
        Customer[Customer Type],
        'Product'[Brand],
        Sales[Due Date],
        TREATAS ( varMaxDates, Sales[Due Date] ),
        "@Sales", [Sales Amount]
    )

That keeps the last 10 dates in the query.