Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

CALCULATE in Power Query M

Hi, please, does anyone know how to bring the last month sales In M Language using this columns as example? the column A is the date and the column B is the Sales Amount.      
Need Help
BA_Pete's avatar
BA_Pete
Super User
2 years ago

Hi Anonymous ,

 

If you just want to filter the table to the latest month, then the following custom step would work:

 

Table.SelectRows(
    PreviousStepName,
    each [data real calculada] = List.Max(PreviousStepName[data real calculada])
)

 

 

Pete

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, i tried to use that one you wrote but didn't work out, because in fact, what i want is bring the sales from last month, so it would be like a function to calculate the sales, but combine with some date function to make sure that i would bring the sales only one month before. 
Here's a DAX example that explain exactly what i need to be done in M Language:

 

Sum SellOut M-1 = var calc =
CALCULATE([Sales Column],DATEADD('a Calendar'[Date],-1,MONTH))
return
IF(calc=0,BLANK(),calc)
  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

     

    Ah, ok. So the DATEADD 'equivalents' in Power Query would be Date.AddDays, Date.AddMonths, or Date.AddYears.

    To filter the table to the prior month relative to today's date/month, assuming your date column always only contains the last date of each month, you would need to find the first day of the current month, take one month from that, then get the last date of that month to filter on, so it would look something like this:

    Table.SelectRows(
    PreviousStepName,
    each [data real calculada] =
        Date.EndOfMonth( Date.AddMonths( Date.StartOfMonth( Date.From(DateTime.LocalNow())), -1))
)

     

    If you want to completely replicate the CALCULATE function and return a scalar value, you would use this filter in conjunction with a Group By or a Statistics function from the Transform tab. However, Power Query isn't designed to be used in this way, so I'd recommend keeping calculational processes in DAX.

     

    Pete