My calendar table has dates from 2012 to 2032. My fact table has dates from 2018 to 2026. Iam restricting my calendar slicer by using count[fact_table] so i get only slice range from 2018 to 2026.
So if i use the date slicer to set a period i want to get all the months even if i dont have data in fact table
Below is the image of my slicer and matrix
I want to see Mar 2020 too. Additionally, the users could completely disregard the date slicer and might want to look at a project
In this case, when i pick project1, it has stages to be completed in may2021,july 2021 and jun2023. So i want to display all the months between may2021 and jun 2023 even if there is no data. The users could choose multiple pojects at a time and in that case i want to show the months between the min date of all the selected projects to the max date of all the selected projects.
For context: The date slicer is using the calendar table and the month_year is coming from calendar table
Link here for pbix file with the issue i have. So when i pick P5 i want to see all months from march 2020 to Oct 2021 only. Either empty or blanks where particular month doesnt have data value will do.
sample screen shot of the output that i need.
Thanks in advance!!
To report on things that are not there you need to use disconnected tables and/or crossjoins. Use a separate table for the months and calculate the status for all projects with a measure.
@lbendlin i created a separate table with month_year and using the measure to get amounts
I used selected value too. I still get the same.
Your measure is missing the SELECTEDVALUE(Month) filter.
Value = CALCULATE(sum(Stage[%complete])/100 *sum(Project[Amount]) ,Stage[Stage_level] <> BLANK() ,format(Stage[Finish Date/Expected Finish date],"mmm yyyy")=SELECTEDVALUE('Table'[Month]) )
This measure needs to be adjusted to reflect your project/stage dependency.
Does this measre work?
Measure = coalesce([your measure],0)
@lbendlin It doesnt work as intended, if i use "show items with no data", i get all the month_year from 2015.
I dont want that, i want to all month_year between my max date measure and min date measure only even if that month_year doesnt have any data.
usually that should work. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.