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.
Hi everyone,
I have database with 2 columns, one is pricingdate and other is market fix rate. so the market fix rate changes from time to time. In my report I added a slicer with calendar dateperiod like last 1 month, 3 months and so on.
This is how it looks like. Now when I select last 6 months I want the result as 1.31147541 but when i select last 9 months i want my result to be 1.301998568.
How do I get it.
Thanks
Solved! Go to Solution.
Hi @pxg08680
I think I understand what you are after.
I created two tables. The first table called dates just carries a single column of dates using this code
Dates = CALENDAR(Date(2010,1,1),TODAY())
The second I hard coded in these three rows (2 columns) , and used this to drive my slicer.
Finally I created the following measure
Measure = var SlicerValue = MAX('Slicer Table'[SlicerID]) var LastDateMonth = LASTDATE(DATEADD('Dates'[Date],-SlicerValue,MONTH)) var LastActualMonth = CALCULATE(LASTDATE('Raw Data'[Date]),'Raw Data'[Date] < LastDateMonth) RETURN CALCULATE(MAX('Raw Data'[MSPricingFXRate]),'Raw Data'[Date] = LastActualMonth)
I created no relationships between any of the tables.
The idea is that when you make a selection on the slicer, the ID column controls how many months you want to jump back to. Then the relevant MSPricingFXRate is returned prior to that jump back in time.
Hi @pxg08680
I think I understand what you are after.
I created two tables. The first table called dates just carries a single column of dates using this code
Dates = CALENDAR(Date(2010,1,1),TODAY())
The second I hard coded in these three rows (2 columns) , and used this to drive my slicer.
Finally I created the following measure
Measure = var SlicerValue = MAX('Slicer Table'[SlicerID]) var LastDateMonth = LASTDATE(DATEADD('Dates'[Date],-SlicerValue,MONTH)) var LastActualMonth = CALCULATE(LASTDATE('Raw Data'[Date]),'Raw Data'[Date] < LastDateMonth) RETURN CALCULATE(MAX('Raw Data'[MSPricingFXRate]),'Raw Data'[Date] = LastActualMonth)
I created no relationships between any of the tables.
The idea is that when you make a selection on the slicer, the ID column controls how many months you want to jump back to. Then the relevant MSPricingFXRate is returned prior to that jump back in time.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |