Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replicate Excel logic in DAX

Hi,

 

I would like to replicate in Power BI, preferabily using DAX, the below excel logic:

1) Combining all the rows of a week (Monday-Sunday). The process in excel currently is the following: the user is adding to the Monday's data the new records found on Tuesday, after this is comparing Monday+new Tuesday's data  with the Wednesdey's records and is adding to the Monday+new Tuesday's data the Wednesday's new records   and so one until Sunday (included).

This combined data is then compared with the next  Monday data and what is not found on Monday's records is marked as Outflow. This process is being repetead for all the following weeks and what I want to retrieve is the number of the records marked as Outflows during a certain week, by creating a first column like the one in the sample - Result - in a new DAX column.

 

Thank you,

Melissa

recordWeekWeekDayDateResult - in a new DAX column
12455421Sunday03/15/2020Outflow - is not appearing in the following Monday
12245442Monday03/16/2020 
12245442Tuesday03/17/2020 
12334432Tuesday03/17/2020Outflow - is not appearing in the following Monday
12245442Thurday03/19/2020 
12245472Sunday03/22/2020Outflow - is not appearing in the following Monday
12245443Monday03/23/2020 
12245443Wednesday03/25/2020Outflow - is not appearing in the following Monday
13345554Monday03/30/2020 

 

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Column = 
VAR x = 
CALCULATE(
    MAX(Sheet1[record]),
    FILTER(
        Sheet1,
        Sheet1[Week] = EARLIER(Sheet1[Week]) + 1  && Sheet1[WeekDay] = "Monday"
    )
)
RETURN
IF(
    Sheet1[record] <> x && Sheet1[WeekDay] <> "Monday",
    "Outflow", BLANK()
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I am not following this, I do not understand why some rows are marked as Outflow and others are not, like one Tuesday is and one is not and also the Thursday.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg_Deckler 

       

      As you can see, that record is found in the next Monday, so it cannot be marked as Outflow.

       

      Thank you,

      Melisa

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

       

      Hi Greg,

       

      Any idea how can I replicate this logic in DAX?

       

      Many thanks,

      Melisa

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Column = 
VAR x = 
CALCULATE(
    MAX(Sheet1[record]),
    FILTER(
        Sheet1,
        Sheet1[Week] = EARLIER(Sheet1[Week]) + 1  && Sheet1[WeekDay] = "Monday"
    )
)
RETURN
IF(
    Sheet1[record] <> x && Sheet1[WeekDay] <> "Monday",
    "Outflow", BLANK()
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.