Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Thanks in advance for help on this.
We use Periods in the format of tax year, month and week. For example, 1901-01 is:
Tax year: 19 (ie 2019)
Tax month: 01 (ie month one of the tax year 26/03/2018 to 22/04/2018)
Tax week: 01 (ie week one out of 4 weeks in given tax month)
We have a data table of product sales:
Product, Department, Qty, Value, Period
with calculated columns based on Period to extract elements:
Period Yr = LEFT('Weekly Data'[PERIOD], 2)
Period Month = LEFT('Weekly Data'[PERIOD], 4)
Period Week = RIGHT('Weekly Data'[PERIOD], 2)
Example:
Product, Department, Qty, Value, Period, Period Yr, Period Month, Period Week
Becks, Draught, 312, 1060.80, 1901-01, 19, 01, 01
Grey Goose, Vodka, 102, 408, 1901-01, 19, 01, 01
Becks, Draught, 300, 1020, 1901-02, 19, 01, 02
Grey Goose, Vodka, 102, 408, 1901-02, 19, 01, 02
Becks, Draught, 312, 1060.80, 1805-03, 18, 05,03
We are wanting to display in a matrix table for a given selected period, the corresponding previous month and also previous year.
For example:
If we selected, say period 1901-01 to display total Value by product, the corresponding previous's to also show.
ie 1801-01 for previous year, and also 1813-01 for previous month. (there are 13 tax months in each given tax year).
Rows:
Department
Product
Columns:
Period Month
Period
Values:
Qty
Value
Again, thanks in advance for help on this matter.
Solved! Go to Solution.
Hi @areadman,
Based on my test, you could refer to below steps:
Create a new table as slicer:
New Table = DISTINCT('Table1'[ Period Yr])
Create a measure:
Measure = CALCULATE(SUM('Table1'[ Value]),FILTER('Table1','Table1'[ Period Yr]>=SELECTEDVALUE('New Table'[ Period Yr])-1&&'Table1'[ Period Yr]<=SELECTEDVALUE('New Table'[ Period Yr])))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @areadman,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi Daniel
Thanks for the response. I'm not sure how your solution works, as I want to compare the relative period last year.
For example if display period 1901, I want to bring up data for 1801 (ie same period, but last year).
Regards
Alex
Hi Daniel
Thanks for the response. I'm not sure how your solution works, as I want to compare the relative period last year.
For example if display period 1901, I want to bring up data for 1801 (ie same period, but last year).
Regards
Alex
Hi @areadman,
Based on my test, you could refer to below steps:
Create a new table as slicer:
New Table = DISTINCT('Table1'[ Period Yr])
Create a measure:
Measure = CALCULATE(SUM('Table1'[ Value]),FILTER('Table1','Table1'[ Period Yr]>=SELECTEDVALUE('New Table'[ Period Yr])-1&&'Table1'[ Period Yr]<=SELECTEDVALUE('New Table'[ Period Yr])))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |