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.
I have a very simple report. One time dimension is linked to the fact table and one time dimension is not. I would like to transfer a slicer selection based on the unconnected time dimension (YYYYMM) to the connected time dimension (max date for the unconnected YYYYMM selection) for a YTD sales metric. I hope the solution for this question will help me for more dynamic time intelligent calculations. Thanks for your help!
Solved! Go to Solution.
Hi @jvandyck
For example, create relationships as below
add "year/month" column from "date table1" to the slicer, then create columns and measure in "fact sales" table
column
year = YEAR('fact sales'[date]) month = MONTH('fact sales'[date])
measures
seelcted_year = SELECTEDVALUE('date table1'[year]) selected_month = SELECTEDVALUE('date table1'[month]) ytd_selected = IF ( MAX ( [year] ) = [seelcted_year] && MAX ( [month] ) <= [selected_month], CALCULATE ( SUM ( 'fact sales'[sales] ), FILTER ( ALL ( 'fact sales' ), [year] = [seelcted_year] && [month] <= [selected_month] && 'fact sales'[date] <= MAX ( 'fact sales'[date] ) ) ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jvandyck
For example, create relationships as below
add "year/month" column from "date table1" to the slicer, then create columns and measure in "fact sales" table
column
year = YEAR('fact sales'[date]) month = MONTH('fact sales'[date])
measures
seelcted_year = SELECTEDVALUE('date table1'[year]) selected_month = SELECTEDVALUE('date table1'[month]) ytd_selected = IF ( MAX ( [year] ) = [seelcted_year] && MAX ( [month] ) <= [selected_month], CALCULATE ( SUM ( 'fact sales'[sales] ), FILTER ( ALL ( 'fact sales' ), [year] = [seelcted_year] && [month] <= [selected_month] && 'fact sales'[date] <= MAX ( 'fact sales'[date] ) ) ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, this works! however I do not fully grasp the logic...can you try to explain?
Also, what would be the formula if I want to show the mtd value for the selected year?
Many thanks again!!
Hi @jvandyck
Let me confirm your requirements.
For example, when you select current year from the unconnected datetable, then the report can show current year's YTD sales and previous year's YTD sales, right?
Best Regards
Maggie
I select a YYYYMM and would like to calculate the YTD up to the max date of that YYYYMM. Thanks for your help!
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |