Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
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
Any suggestions?
https://drive.google.com/file/d/14309w8E2a5nXjcHhbzYPuC0Oim1ITinQ/view?usp=share_link
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.
@Ashish_Mathur @lbendlin
Hope this helps in understanding my issue.
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
Your measure is missing the SELECTEDVALUE(Month) filter.
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.
See attached.
Hi,
Does this measre work?
Measure = coalesce([your measure],0)
"show items with no data"
@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).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
95 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
109 | |
98 | |
93 |