filter a summarised table by date

[gtamir]

Hi, I have a summarised table based on the transaction table.

Then I have a page with visuals using measures based on the summarised table.

Now I want to filter the visuals to one month only. Is it possible?

[Greg_Deckler]

Depends but should be possible. However, a definitive answer would require additional information. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

You can join your summarized table to your Calendar table and filter ?

[gtamir]

@amitchandak How? there is no common field.

[amitchandak]

Does that table has Month, Then use that as a filter. I think I am confused now. Please explain the issue with an example.

[gtamir]

All the visuals are based on measures

[Greg_Deckler]

Right, but we have no idea what your measure formulas are so kind of hard to know if they would respond to filters by month.

But in any case, try changing the Cross filter direction between your circled table and your Tipulim table to Both. Even though there is no common field between the tables, if Calendar filters Tipulim and Tipulim filters your circled table, then Calendar ultimately filters your circled table. 

The other thing that you can do is to build the relationship directly into the measure. In other words, use something like:

VAR __Month = MAX('Calendar'[Month])

You put that in your measure and now you can use ___Month variable within your measure to do filtering, etc.

[gtamir]

I continued to investigate.

I put on the visual of the filtered page all the fields of "RunningTotalByMetupalim". The table is filtering the names for the last month,

but the column "Total" gives the total of all the unfiltered table. This is why all the table is wrong.

The summarised table is: 

RunningTotalByMetupalim = SUMMARIZECOLUMNS(Tipulim[Metupal Name], "TOTAL" , SUM(Tipulim[Total]))

[gtamir]

@Greg_DecklerI did the first thing, it filters but wrongly. The number of customers is correct, but other values are wrong. I think it will never work.

You see, the summarised table contains other calculated columns based on the original transactions table. They will not be filtered.

I think I will have to do a different project every month.

What do you say?

[amitchandak]

Have you created a separate measure table and referring to that.  This means a dummy table with the only measure.

In that, case your measures will respond to the filter of the tables they belong too.

If you have created a summarized table without a date or month, then you have to create it with a date. You can take month start date to group data at month level and join it with the date table.