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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
FredLEGUEN
Helper III
Helper III

How to filter a chart on the last day of the previous month

Good morning all,

 

I'm working on an existing Power BI report where the source is the result of a complex SQL query. The problem is not to build a real Power BI report (Power Query, measures, Data Model) but to return the same results as the previous version.

For all the tables / card and so on, no problem, the result is similar between both techniques but not for some line charts.

 

For instance, the old chart doesn't display the values for the current month (the blue-light line) but with my chart, I see the values for September (and that's not good)
Screenshot Correct Situation.pngScreenshot My solution.png

 

Both chart have the same filter and the source for the 2 charts have data for September

Screenshot Filter.png

 

Do you know what I should do to not represent the data of the current month in my chart?

 

Thnaks

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@FredLEGUEN now I see the issue, it means you will have two relationships with the fact table and I believe there is an inactive relationship with skClearedDate, create athe  new measure using following and then use it in the visual and use date from date dimension for relative filtering

 

Clear Date Measure = 
CALCULATE ( 
   <<measure you want to use>>,
   USERELATIONSHIP ( FactTable[skClearDate], DateDimenstion[Date] )
)

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@FredLEGUEN now I see the issue, it means you will have two relationships with the fact table and I believe there is an inactive relationship with skClearedDate, create athe  new measure using following and then use it in the visual and use date from date dimension for relative filtering

 

Clear Date Measure = 
CALCULATE ( 
   <<measure you want to use>>,
   USERELATIONSHIP ( FactTable[skClearDate], DateDimenstion[Date] )
)

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

That's it.

The problem was the relationship between my tables

Thanks @parry2k for your help

parry2k
Super User
Super User

@FredLEGUEN what do you see in the date range?

 

parry2k_0-1662497232284.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k ,

The relationship between my timetable and my fact table is on the Due Date. But for this chart, the relationship should be with the skClearedDate

parry2k
Super User
Super User

@FredLEGUEN and also can you make sure the column on the x-axis is used from the correct table. 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@FredLEGUEN what do you see the date range on the filter where you see the current month data?

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,
Can you rephrase your question, I don't understand what I have to check
Otherwise my date on my X-axis is correct considering my data model

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.