Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
hanygameel
Helper I
Helper I

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

hanygameel_0-1692522328242.png

 

1 ACCEPTED SOLUTION
hanygameel
Helper I
Helper I

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

 
WhatsApp Image 2023-08-21 at 9.29.15 PM.jpeg



View solution in original post

6 REPLIES 6
hanygameel
Helper I
Helper I

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

 
WhatsApp Image 2023-08-21 at 9.29.15 PM.jpeg



Idrissshatila
Super User
Super User

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 💡



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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

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

 

some_bih
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.