Forum Discussion
Need previous period logic
Hi All,
i need logic for previous metric.
there are 2 filters period type and period name
when i select period type=month and period name=May or period name = (May ,april)
current should show may data and previous should show april data
similarly when i select period type=month and period name=april or period name=(april,march)
current=april data and previous=march data
similarly when i select period type=Qtr and period name=Q2 or period name=(Q2,Q1)
current=Q2 data and previous=Q1 data
need you help in getting previous metric when we select above filter scenarios(either selecting period name current or current & previous would work)
I was not able to find how to attach PBIX file so i am pasting the sample data and output required below -
| Period Key | Period Type | Period Name | Date | Group | Quantity |
| 1 | Month | May | 12-May | A | 20 |
| 1 | Month | May | 15-May | A | 5 |
| 1 | Month | Apr | 7-Apr | A | 15 |
| 1 | Month | Apr | 15-Apr | A | 10 |
| 1 | Month | Mar | 23-Mar | A | 5 |
| 1 | Month | Feb | 15-Feb | A | 20 |
| 1 | Month | May | 15-May | B | 20 |
| 1 | Month | Apr | 10-Apr | B | 30 |
| 1 | Month | Mar | 28-Mar | B | 30 |
| 1 | Month | Feb | 11-Feb | B | 10 |
| 2 | Qtr | Q2 | 12-May | A | 20 |
| 2 | Qtr | Q2 | 15-May | A | 5 |
| 2 | Qtr | Q2 | 7-Apr | A | 15 |
| 2 | Qtr | Q2 | 15-Apr | A | 10 |
| 2 | Qtr | Q1 | 23-Mar | A | 5 |
| 2 | Qtr | Q1 | 15-Feb | A | 20 |
| 2 | Qtr | Q2 | 15-May | B | 20 |
| 2 | Qtr | Q2 | 10-Apr | B | 30 |
| 2 | Qtr | Q1 | 28-Mar | B | 30 |
| 2 | Qtr | Q1 | 11-Feb | B | 10 |
below should be the output when i select below filters
when i select period type=month and period name=april or period name=(april,march)
current=april data and previous=march data
| Group | Current | Previous |
| A | 25 | 5 |
| B | 20 | 30 |
Thanks,
Raj
7 Replies
ryan_mayuSuper User
Anonymous
I create a datetime table and create relationship between fact and datetime table.
You can create quarter ,month name in the datetime table.
Then you create a measure for previous month qty.
Measure = CALCULATE(sum('table'[Quantity]),DATEADD('datetime'[Date],-1,MONTH))The month column you used for the filter should come from datetime table.
how does it work when i select quarter? even i have week,year,quarter,month,LTD as values in period type and relevant values in period name
ex - when i select period type=Qtr and period name=Q2 or period name=(Q2,Q1)
current=Q2 data and previous=Q1 dataUI looks something like this. period type and period name are like relevant filters.
- Ashish_Mathur
Hi,
In your Data, what is the relevance of the Period Type and Period Names columns? The Month and Quarter should get deterined from the Date column itself. Also, if the user selects only 1 month, then you can get your desired result.
Please let me know.
- ryan_mayu
Anonymous,
Create a measure to calculate last quarter value.
Measure = CALCULATE(SUM(Sheet11[Quantity]),DATEADD('datetime'[Date],-1,QUARTER))Quarter filter should also come from the datetime table.
