Filter context issue

[LorenzoNieri]

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

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

[v-xicai]

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]

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]

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

[pranit828]

HI @LorenzoNieri 

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

Select "Before" then use the filter.