Forum Discussion

Anton_Dali's avatar
Anton_Dali
New Member
3 years ago
Solved

Group and filter by Date

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?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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