Group and filter by Date

[Anton_Dali]

Hello community.

I have a query for table where financial results are collected. One of the step defines the new date "A_F_Date" based on simple formula. In this formula I need to put additional checking of the result for previous calendar month regarding TypeAnalysis where few options are available. This must be done in query not in the dax, because matrix visual is already using single column as value.

My idea is 

IF sum of previous month > 0 OR is not BLANK

else Date.StartOfMonth(Date.AddMonths(DateTimeZone.UtcNow(), -1))

than Date.StartOfMonth(Date.AddMonths(DateTimeZone.UtcNow(), -2))

Each date apllied as a first day of the month in datatable

Could you please help me to complete expression, so proper "A_F_Date" date will be determined on the flight?

[Anonymous · Accepted solution]

Hi @Anton_Dali ,

Please add a custom column:

let
  pre = try
    Table.SelectRows(
      Table.Group(PreviousStepName, {"TypeAnalysis", "Date"}, {{"sum", each List.Sum([orgValue])}}),
      (x) => x[TypeAnalysis] = [TypeAnalysis] and Date.AddMonths([Date], - 1) = x[Date]
    )[sum]{0}
  otherwise
    null,
  result =
    if pre > 0 or pre = null then
      Date.StartOfMonth(Date.AddMonths(DateTimeZone.UtcNow(), - 2))
    else
      Date.StartOfMonth(Date.AddMonths(DateTimeZone.UtcNow(), - 1))
in
  result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum