Date not summarising by month correctly

[Nandeep]

Hi,

I have a fact table(Alldata) whihc has every single transaction with premiums by product and key account. I have another table called Plan. Which shows the plan figure for each keyaccount by month. When i create a visual and then select a particular month, the plan figure seems to be woking correctly down to key accountleval, however the actuals (from Alldata) is not splitting it by key ccount but the premium is not for the month selected but for all the data within the dataset.

[Anonymous · Accepted solution]

Hi, @Nandeep 

hanks for the reply from @Greg_Deckler , please allow me to provide addition:

Make sure that there is a correct relationship between your table and any date tables or tables that may control the selection of months. This relationship should allow filtering across tables. If the relationship is set up incorrectly, you might not be able to filter the data as expected. Learn more about managing relationships in Power BI:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

In Power BI, filter context is critical to determining the data that appears in a visual. Make sure that the visual is set to follow the filter context passed by the month selection. This may involve adjusting how the visual interacts with slicers or other visuals on the report page. 

If you rely on Power BI's automatic date/time feature to implement a date hierarchy, be aware that it has some limitations. For example, in some model designs, it may not always propagate the filter as expected. Consider creating a custom date table to give you more control over date hierarchies and filtering. You can check the following link:

Auto date/time guidance in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Nandeep]

below is a pic of the matrix, the PlanAmount is summing correctly, but the Notional Gross Premium is not summing by month

[Greg_Deckler]

@Nandeep First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8