YTD Amts by Month and MTD Amts by Week Based on Only the Week Slicer

[Magellan2022]

Hello,

I'm trying to show monthly amounts in a bar graph that has the Month Name on Y-axis and Revenue amount on the X-axis. Additionally, I'm also trying to achieve MTD amounts in a bar graph that has Week End Date on the Y-axis and Revenue on the X-axis. The issue I'm trying to solve is when a user selects a Week End Date value from the slicer ONLY(no other date slicers selected), then I want the YTD graph to show amounts from the start of the fiscal year up to the Week End Date(slicer) selected displayed by Month Name in the graph. I also want the MTD graph to show amounts from the start of the fiscal month up to the Week End Date(slicer) selected displayed by Week End Date in the graph. I'm also trying to achieve the same behavior with both graphs when all date slicers are blank/deselected but the Default Week slicer is set to "Yes" which signifies the current week.

For each fiscal year in the custom calendar, the dates for which a fiscal year and a fiscal month starts will vary for each fiscal year you look at. As a result, time-intelligence functions won't help here.

Requirements

1.) If a user selects the Week End Date slicer value of 4/8/2023, then the YTD graph by Month Name should show revenue amounts starting from the beginning of fiscal year 2023 up to 4/8/203.

2.) If a user selects the Week End Date slicer value of 4/8/2023, then the MTD graph by Week Date End should show revenue amounts starting from the beginning of fiscal month April up to 4/8/203 effectively showing the first two weeks of fiscal month April.

3.) If the Default Week slicer is "Yes" which in my Dim Date is the Week End Date of 1/13/2024, then then the YTD graph by Month Name should show revenue amounts starting from the beginning of fiscal year 2024 up to 1/13/204.

4.) If the Default Week slicer is "Yes" which in my Dim Date is the Week End Date of 1/13/2024, then then the MTD graph by Week Date End should show revenue amounts starting from the beginning of fiscal month January up to 1/13/2024.

Between disabling and enabling the "Edit interactions" between the slicers and visuals and coding custom DAX logic, I'm not sure if I can achieve what I want with only one Dim Date in the model. I fee like I need a second Dim Date that has no relationships in the data model to fulfill the functionality I'm going for here but I'm not 100% sure.

Here is the link to the PBIX file itself. https://drive.google.com/file/d/1I-W4wM5HGeZmmHbVkMOxVsoQVbvO4mIe/view?usp=sharing 

Any initial thoughts ?

[v-heq-msft]

Hi @Magellan2022 ,
According to what you have mentioned, you can create a new table containing the columns you need, making sure that the new table does not have any relationship with the original table, and you can create a measure to be placed on the visual-level filter of the bar charts you want to make calculations for YTD and MTD to show the total from the beginning of the fiscal year to the selected date.
You can refer to this answer to verify if it troubleshoot your problem

Solved: Need help in creating vizualization with slicer - Microsoft Fabric Community

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
 

[Magellan2022]

@v-heq-msft ,

With your approach you presented in the link how would I calculate the total based on the amounts shown if the visual was a matrix instead of a bar graph?