Display Previous Data Based on User Selection Using Date and Periodic Calendar Tables

[PowerBI-Newbie]

Hi,

I have the following data:

The first is the calendar table and the second is the data table. I have the two date columns linked but when I report, I need to report using the period column. The reporting is in the form of a filtered table and line graph, counting the number of times a particular Area and/or Sub-Area appears. E.g. below:

Period	Area	Count
1	Area 1	1
1	Area 2	1
2	Area 1	2
2	Area 2	1

Period	Area	Sub-Area	Count
1	Area 1	Sub-Area 1a	1
1	Area 2	Sub-Area 2a	1
2	Area 1	Sub-Area 1a	2
2	Area 2	Sub-Area 2a	1

So when I select Period 1 from my related slicer, I should be able to see Period 1 data which I do. However, whenever I select Period 2 for example it only displays Period 2 and doesn't display Periods 1-2 which is what I want it to do. So the data for the line chart should display Period 1 to Period X, and X being the user selection.

Please note that the slicer is used to calculate other values other than producing the above outputs.

[Anonymous · Accepted solution]

Hi @PowerBI-Newbie,

It sounds like a common requirement to filter on the date range that generated from two date fields, perhaps you can take a look at the following link 'start date', 'end date' part.

Before You Post, Read This 

In addition, you can also try to create a calculated table to expand the date range from row table fields, then you can simply filter on the date range records:

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng

[amitchandak]

@PowerBI-Newbie , Not very clear. If you can have incremental on period prefer to have YYYYPP .

Try like

Period Rank = RANKX(all('Date'),'Date'[year period],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

[PowerBI-Newbie]

Hi @amitchandak , let me know what's unclear.

I'm trying to count the number of concerns raised in any given period for each Area or Sub-Area (depending on slicer selection)  and the result in given to me as a table and line graph - so the user selects Period 1 and all the concerns raised in Period 1 will be broken down by Area and Sub-Area; if they select Period 2 then it will show Period 1 as well as Period 2. The line graph will show the figure for Period 1 when Period 1 is selected, and it will show Periods 1 and 2 figures when Period 2 is selected.

I hope that makes sense.