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...
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
        )
     
    -----

     



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.