Forum Discussion
SLICER AS A FILTER FOR DATA TABLE
Hi
I need help with dax. I have three tables:
- a calendar table
- a data table (fact table)
- a table with ratios
The ratios table is as follows:
| Date | Date Analysis | Ratio |
| Jan-20 | Jul-20 | 1,01 |
| Feb-20 | Jul-20 | 1,02 |
| Mar-20 | Jul-20 | 1,05 |
| Apr-20 | Jul-20 | 1,07 |
| May-20 | Jul-20 | 1,03 |
| Jun-20 | Jul-20 | 1,02 |
| Jul-20 | Jul-20 | 1 |
| Jan-20 | Aug-20 | 1,084 |
| Feb-20 | Aug-20 | 1,077 |
| Mar-20 | Aug-20 | 1,07 |
| Apr-20 | Aug-20 | 1,06 |
| May-20 | Aug-20 | 1,05 |
| Jun-20 | Aug-20 | 1,03 |
| Jul-20 | Aug-20 | 1,02 |
| Aug-20 | Aug-20 | 1 |
For each month of Date Analysis, I have a ratio associated to each previous month.
In the dashboard I have a Date slicer (calendar field) and measures that need to be adjusted by the correspondent ratio for each month depending on the selected month in the slicer.
For ex.
- If the user selects July/20 in the slicer, then the measures need to be adjusted: january by 1,01, february by 1,02 ...
- If the user selects August/20 in the slicer, then te measures need to be adjusted: january by 1,084, february by 1,077 ...
Do you have any idea hoy can I solve this?
I would really appreciate your help.
Hi JOKA ,
We can create a measure to meet your requirement.
1. Create the relationships like following screenshot.
2. Then we can create a measure like this,
Indicator Total correct = IF ( HASONEFILTER ( 'Fact Table'[Date] ) || HASONEFILTER ( 'Table'[Date] ), SUM ( 'Fact Table'[Metric] ) * SUM ( 'Table'[Ratio] ), SUMX ( SUMMARIZE ( 'Fact Table', 'Fact Table'[Date], 'Fact Table'[Metric], "product", 'Fact Table'[Metric] * CALCULATE(SUM('Table'[Ratio])) ), [product] ) )If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
4 Replies
JOKA , not very clear to me but can YTD help
Example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38aThank you for your response! I appreciate it.
Let me give you an example to explain a bit more the problem:
These are the tables I have
In the dashboard I have a slicer that uses the field Date in Calendar Table. If I select August in the slicer, I want to see this:
Date Indicator Jan-20 1626 Feb-20 1292,4 Mar-20 1391 Apr-20 954 May-20 1050 Jun-20 916,7 Jul-20 1224 Aug-20 1600 the formula = Metric x Ratio
where Date Analysis = Aug-20
I´m attaching an excel file with the example here:
https://drive.google.com/file/d/1jgJvsV-QVb42bl3VIi_aFXUjQj-Jk_2u/view?usp=sharing
Hi JOKA ,
We can create a measure to meet your requirement.
1. Create the relationships like following screenshot.
2. Then we can create a measure like this,
Indicator Total correct = IF ( HASONEFILTER ( 'Fact Table'[Date] ) || HASONEFILTER ( 'Table'[Date] ), SUM ( 'Fact Table'[Metric] ) * SUM ( 'Table'[Ratio] ), SUMX ( SUMMARIZE ( 'Fact Table', 'Fact Table'[Date], 'Fact Table'[Metric], "product", 'Fact Table'[Metric] * CALCULATE(SUM('Table'[Ratio])) ), [product] ) )If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi JOKA ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
