cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LorenzoNieri
Frequent Visitor

Filter context issue

Hello, I have an issue with filter context in dates. So, basically I have built up all my report based on the filter Single Year and Single Month in all pages, for example year 2020, month of year 5. The data model has a time table (tim dim) which is connect to the fact sales based on date key, nothing more than standard by now. The problem is that I'm trying to calculate the YTD sales and show the running total of january, feb, march until the selected month. I tryied with this measure

 

CALCULATE([Sales YTD],ALL('time dim'[MonthOfYear]))

 

Sales PY YTD= TOTALYTD([sales],'time dim'[Date])

 

But the problem is that the month of year is not appying instead I would like a table that stops at that month. This is what I get

ex problema pbi.PNG

 

For instance if I have select year 2020, month 5 I would like to see

 

Month  Salels Ytd

JAN          10

FEB           30

MAR         50

APR          80

MAY        110

I've tried many solution but none of them worked could you help me please

 

Thanks

 

Lorenzo

4 REPLIES 4
v-xicai
Community Support
Community Support

Hi  @LorenzoNieri  ,

 

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

v-xicai
Community Support
Community Support

Hi @LorenzoNieri ,

 

For the relationship between your fact data table and 'time dim' , you may keep the Cross filter direction as Both instead of Single,  ,which will take these tables treated as a single table. Then when you make some changes or interact in someone table, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AllisonKennedy
Super User III
Super User III

@LorenzoNieri
The screenshot you shared of SalesYTD seems to be working as expected - what DAX have you used for that? You have referred to [SalesYTD] inside your DAX in this post, but haven't shared what formula [SalesYTD] uses.

The YTD DAX measures add up all the months prior to the selected month, so for the current month and any future months it will display the same value, as there are no additional sales in the coming months to make that count up, but also there is no negative sales to reset it to blank or zero, so it stays the same as for the latest value.

Can you share a wider screenshot with your date filter and show which dates you're using in the filter and which in the visual where you'd like to see the monthly YTD totals? You may need to get a little creative to make this work if you want to have only 1 month selected.


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

pranit828
Super User II
Super User II

HI @LorenzoNieri 

 

There is a little dropdown on right top of the filter of the month,

Select "Before" then use the filter.

pranit828_0-1597568267318.png

 




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.