Forum Discussion

hanygameel's avatar
hanygameel
Helper I
3 years ago
Solved

i Have Problem with date table

i Have Problem with date table
i make date table and make relationship with 6 data table

when i make visulization and make the date as filter 

give me and dates not only the date for data selected 
 below data for 2 month
jan 2023
Augest 2023

 

Need Help
  • hanygameel's avatar
    hanygameel
    3 years ago

    thanks for all 
    i got it
    the problem solved by make measure to make filter by it to display date for invoice date in the data table

    DAX :

    SELECTED DATE =
    VAR SelectedInvoiceDate = SELECTEDVALUE('Date'[Date])
    VAR MINDATE =
        MINX(
            UNION(
                '01Riyadh',
                '01Riyadh-2',
                '02BUR1',
                '02BUR1-2',
                '03BUR2',
                '03BUR2-2',
                '04RAFHA',
                '04RAFHA-2'
            ),
            [INVOICE_DATE]
        )
    VAR MAXDATE =
        MAXX(
            UNION(
                '01Riyadh',
                '01Riyadh-2',
                '02BUR1',
                '02BUR1-2',
                '03BUR2',
                '03BUR2-2',
                '04RAFHA',
                '04RAFHA-2'
            ),
            [INVOICE_DATE]
        )
    RETURN
        IF(
            AND(
                SelectedInvoiceDate >= MINDATE,
                SelectedInvoiceDate <= MAXDATE
            ),
            1,
            0
        )
     
    -----

     



6 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi hanygameel two or more Date tables are not good practice, unless you know what are you doing.

    Go to your model and make single Date table. This mean you need to rework your measure and / or model overall.

    • hanygameel's avatar
      hanygameel
      Helper I
      3 years ago

      I have already one date table connected  y relationship to 6 Data table

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    3 years ago

    Hello hanygameel ,

     

    so when you create a calendar table, it calculate daily dates from the minimum date in you model to the maximum, so what can I guess is that there is maybe a row in all these data table has the date back in 1921 and that why this calendar table is generating data all the way from this date to 2023.

     

    check all your data table by sorting them ascending order to see which one has this record and then remove it in power query if there's no need for it.

     

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

    Follow me on Linkedin
    Vote For my Idea 💡

    • hanygameel's avatar
      hanygameel
      Helper I
      3 years ago

      I generate date table by bravo tools 

      because I have more column has dates

      like birth date 

      invoice date

      discharge date

       

      the tools get all date availabile in my data tables

      and 1921 mybe a birth date of old patient

      • foodd's avatar
        foodd
        Community Champion
        3 years ago

        hanygameel,   Idrissshatila with both kindness and gentleness suggested that the issue relates to your model and that you should put the effort in to identify and eliminate any duplicates, ensure table relationships are correct, and ensure the calendar table satisfies the needs of the model.   some_bih also voiced the same concerns and that you should spend some quality time with your model.    Adding your work-in-progress Power BI Desktop file to your post would be helpful at this time.

         

  • hanygameel's avatar
    hanygameel
    Helper I
    3 years ago

    thanks for all 
    i got it
    the problem solved by make measure to make filter by it to display date for invoice date in the data table

    DAX :

    SELECTED DATE =
    VAR SelectedInvoiceDate = SELECTEDVALUE('Date'[Date])
    VAR MINDATE =
        MINX(
            UNION(
                '01Riyadh',
                '01Riyadh-2',
                '02BUR1',
                '02BUR1-2',
                '03BUR2',
                '03BUR2-2',
                '04RAFHA',
                '04RAFHA-2'
            ),
            [INVOICE_DATE]
        )
    VAR MAXDATE =
        MAXX(
            UNION(
                '01Riyadh',
                '01Riyadh-2',
                '02BUR1',
                '02BUR1-2',
                '03BUR2',
                '03BUR2-2',
                '04RAFHA',
                '04RAFHA-2'
            ),
            [INVOICE_DATE]
        )
    RETURN
        IF(
            AND(
                SelectedInvoiceDate >= MINDATE,
                SelectedInvoiceDate <= MAXDATE
            ),
            1,
            0
        )
     
    -----