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 try to put that column in the slicer that filters my date

The problem is the 30 days give me 29 days

and the 90 days bring me 60 days

I tried formulas and a calculated column but without success
I would appreciate your help
Attach the file

Exmple Date.pbix

 

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!

     

5 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello netanel ,

     

    so this is calculated wrong from the db, because when I tried filtering the table from the data view to see the rows related to last 30 days it showed 29 as you can see in the screenshot.

     

    And when i filtered it to show the last 90 days it only filtered 60 rows

     

     

    so you should check the calculation of this column from the database calculation because power bi is reflecting it as it was calculated.

     

    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

      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 💡