Forum Discussion
Extracting data Multiple Columns from current period and previous period.
I know this is possible point by point with measures however I'm looking for a more efficient method.
I'm looking for a method to create a table drawing data from multiple columns with the ability to pull value totals from specific periods in the same table (see the table below).
| Current Period | Previous Period | Period Last Year | |
| Column 1 | CP1 | PP1 | LY1 |
| Column 2 | CP2 | PP2 | LY2 |
| Culumn 3 | CP3 | PP3 | LY3 |
| Column 4 | CP4 | PP4 | LY4 |
Note that I'm happy to do this via measures or by creating separate columns for each period but I would like to find a measure or method that can be used on other data columns (I have seen it done but the method was sealed in black box style so I do not know how they performed it).
ADDED
To clarify, here is example data, I can't give real data in this case
| Column 1 | Column 2 | Column 3 |
01/01/2019 | 798.62 | 1857.8 | 18.29 |
01/02/2019 | 3875.35 | 1060.91 | 1372.83 |
01/03/2019 | 2668.82 | 913.88 | 3254.34 |
01/04/2019 | 4606.96 | 3737.38 | 3214.94 |
01/05/2019 | 1499.23 | 2000.9 | 530.95 |
01/06/2019 | 2851.45 | 4927.25 | 3552.98 |
01/07/2019 | 2708.88 | 4513.48 | 3092.84 |
01/08/2019 | 3863.06 | 4822.98 | 261.51 |
01/09/2019 | 3123.16 | 1869.86 | 3381.77 |
01/10/2019 | 2470.37 | 3292.95 | 4851.91 |
01/11/2019 | 875.2 | 1407.11 | 3949.4 |
01/12/2019 | 3648.4 | 1394.41 | 4251.41 |
01/01/2020 | 1883.55 | 3474.14 | 4148.61 |
01/02/2020 | 3106.72 | 2306.76 | 2617.62 |
01/03/2020 | 4333.57 | 577.03 | 3459.66 |
01/04/2020 | 2508.87 | 3815.85 | 3567.21 |
01/05/2020 | 3192.02 | 3265.59 | 504.31 |
01/06/2020 | 2262.83 | 1413.5 | 1422.37 |
01/07/2020 | 4845.29 | 1504.92 | 1815.11 |
01/08/2020 | 3984.53 | 770.02 | 943.56 |
01/09/2020 | 1816.41 | 2746.66 | 4392.3 |
01/10/2020 | 4790.29 | 3427.69 | 4489.06 |
01/11/2020 | 4656.94 | 1781.59 | 27.77 |
01/12/2020 | 1617.62 | 2179.25 | 4970.5 |
And this is the final table I want, note I want to be able to be able to do this for day, week, month and quarter.
| Current Month | Previous Month | Previous Year |
Column 1 | 1617.62 | 4656.94 | 3648.4 |
Column 2 | 2179.25 | 1781.59 | 1394.41 |
Column 3 | 4970.5 | 27.77 | 4251.41 |
2 Replies
- HotChilli
Community Champion
Can you provide some sample data please (not a picture)? ... and tell us what it is you want a little more clearly please.
Perhaps you could point us to where you've seen it done -> "I have seen it done "
- amitchandak
Super User
IanCF , not very clear. If you need a dynamic measure approach, refer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115//using the tabular editor, more options
https://www.youtube.com/watch?v=vlnx7QUVYMEIf case you need time intelligence, this period vs last period, etc
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
