Forum Discussion

netanel's avatar
netanel
Post Prodigy
2 years ago
Solved

Date Button

Hi All!   I need your help   I Created in my DB column in the dim date that is called "Date Button" The column contains three categories Yesterday 30 days back 90 days back   Now I t...
Need Help
  • netanel's avatar
    netanel
    2 years ago

    Hi All,

    I found the solution
    1. First open a new table

    and insert that Dax code:

    Date Buttons =
    VAR Yesterday =ADDCOLUMNS(
            CALCULATETABLE(
                'Dim_Date',
                FILTER(Dim_Date, Dim_Date[Date] = TODAY() - 1)
            ),
            "Last X Days", " "
        )

    VAR Last30Days =ADDCOLUMNS(
            CALCULATETABLE(
                'Dim_Date',
                DATESBETWEEN(
                    Dim_Date[Date],
                    TODAY() - 30,  // Adjust the start date to exclude today
                    TODAY() - 1   // Adjust the end date to include yesterday
                )
            ),
            "Last X Days", "  "
        )

    VAR Last90Days =ADDCOLUMNS(
            CALCULATETABLE(
                'Dim_Date',
                DATESBETWEEN(
                    Dim_Date[Date],
                    TODAY() - 90,  // Adjust the start date to exclude today
                    TODAY() - 1   // Adjust the end date to include yesterday
                )
            ),
            "Last X Days", "   "
        )
        VAR Last1737Days =
        ADDCOLUMNS(
            CALCULATETABLE(
                'Dim_Date',
                DATESBETWEEN(
                    Dim_Date[Date],
                    TODAY() - 1737,  // Adjust the start date to exclude today
                    TODAY() - 1   // Adjust the end date to include yesterday
                )
            ),
            "Last X Days", "    "
        )

    RETURN UNION(Yesterday, Last30Days, Last90Days,Last1737Days)

    This table will add a new dim date that collects all the buttons in one bucket
    For example yesterday you got 3 rows 
    yesterday
    Last 30 Days 
    Ans Last 90 days

    2. Step two connect to your dim date
    many to one and connection both side

    Good luck!

     

netanel's avatar
netanel
Post Prodigy
2 years ago

But that's the whole idea, think about it
In SQL I bring one column
Yesterday is one  line
Last 30 Days can't register on yesterday
The last 90 Days  cannot be registered on 30 days and yesterday

Now in Power BI, I am trying to calculate a measure
That 30 days back will bring me both yesterday and the 30 days

Beyond that, you have a separate column for each date as well
Last 30 Days whit 30 rows
Last 90 Days whit 90 rows

 

Idrissshatila's avatar
Idrissshatila
Super User
2 years ago

Hello netanel ,

 

So i adjusted the last 30 days measure to the following:

 

Last 30 Days =
VAR Last30Days = MAX('Dim_Date'[ISODateName]) -30

RETURN
SWITCH(TRUE(),
'Dim_Date'[ISODateName] > Last30Days,"Last 30 days")
 

 

 

And the last 90 days to the following

 

Last 90 Days =
VAR Last90Days = MAX('Dim_Date'[ISODateName]) -90

RETURN
SWITCH(TRUE(),
'Dim_Date'[ISODateName] > Last90Days,"Last 90 days")
 

 

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

 

  • netanel's avatar
    netanel
    Post Prodigy
    2 years ago

    Idrissshatila 

    That doesn't answer my question...
    I need one measure to insert in the slicer

    And get 30 days for "Last 30 Days"
    And 90 Days for "Last 90 Days"

    and also Yesterday

     

    • netanel's avatar
      netanel
      Post Prodigy
      2 years ago

      Hi All,

      I found the solution
      1. First open a new table

      and insert that Dax code:

      Date Buttons =
      VAR Yesterday =ADDCOLUMNS(
              CALCULATETABLE(
                  'Dim_Date',
                  FILTER(Dim_Date, Dim_Date[Date] = TODAY() - 1)
              ),
              "Last X Days", " "
          )

      VAR Last30Days =ADDCOLUMNS(
              CALCULATETABLE(
                  'Dim_Date',
                  DATESBETWEEN(
                      Dim_Date[Date],
                      TODAY() - 30,  // Adjust the start date to exclude today
                      TODAY() - 1   // Adjust the end date to include yesterday
                  )
              ),
              "Last X Days", "  "
          )

      VAR Last90Days =ADDCOLUMNS(
              CALCULATETABLE(
                  'Dim_Date',
                  DATESBETWEEN(
                      Dim_Date[Date],
                      TODAY() - 90,  // Adjust the start date to exclude today
                      TODAY() - 1   // Adjust the end date to include yesterday
                  )
              ),
              "Last X Days", "   "
          )
          VAR Last1737Days =
          ADDCOLUMNS(
              CALCULATETABLE(
                  'Dim_Date',
                  DATESBETWEEN(
                      Dim_Date[Date],
                      TODAY() - 1737,  // Adjust the start date to exclude today
                      TODAY() - 1   // Adjust the end date to include yesterday
                  )
              ),
              "Last X Days", "    "
          )

      RETURN UNION(Yesterday, Last30Days, Last90Days,Last1737Days)

      This table will add a new dim date that collects all the buttons in one bucket
      For example yesterday you got 3 rows 
      yesterday
      Last 30 Days 
      Ans Last 90 days

      2. Step two connect to your dim date
      many to one and connection both side

      Good luck!