Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Saga
Helper I
Helper I

All the months between min and max date which comes from 2 measures

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

Saga_1-1680099266056.png

I want to see Mar 2020 too. Additionally, the users could completely disregard the date slicer and might want to look at a project

Saga_2-1680099648868.png

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

Saga_3-1680100143561.png

 

 

Any suggestions?

 

 

 





 

 

 

10 REPLIES 10
Saga
Helper I
Helper I


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.

Saga_0-1680814118611.png

 

@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

Value =
 CALCULATE(
                (sum(Stage[%complete])/100) *sum(Project[Amount])
                            ,FILTER( Stage, Stage[Stage_level] <> BLANK() )
             )
and i get  Saga_0-1681146683814.png

 

I tried to get the max and min date and used it in the measure, still same output.
What am i missing? I have not worked with disconnected table before. Any help is greatly appreciated!
 
Thanks in advance.

Your measure is missing the SELECTEDVALUE(Month) filter.

I used selected value too. I still get the same.

Saga_0-1681156525464.png

Value_test =
 CALCULATE(
                (sum(Stage[%complete])/100) *sum(Project[Amount])
                            ,FILTER( Stage, Stage[Stage_level] <> BLANK() )
                            // , FILTER(Stage, Stage[Finish Date/Expected Finish date] >= [min_test] && Stage[Finish Date/Expected Finish date] <= [max_test] )
                             , FILTER(MonthShortName_Year, MonthShortName_Year[Month Year] >= [min_test] && MonthShortName_Year[Month Year] <= [max_test] )
             )
 
min_test =
var project1 = AllSELECTED(Project[Project])
var min_sort_order = CALCULATE( MIN(CALENDAR[Sort Order Year Month]), FILTER(Stage, Stage[Project] in project1)
                                                                         , FILTER(Stage, not isblank(Stage[Stage_level] )) )
return
 
  CALCULATE( MIN('CALENDAR'[MonthShortName_Year]), FILTER ('CALENDAR', 'CALENDAR'[Sort Order Year Month] = min_sort_order ) )
 
max_test =
var project1 = AllSELECTED(Project[Project])
var max_sort_order = CALCULATE( Max('CALENDAR'[Sort Order Year Month]), FILTER(Stage, Stage[Project] in project1)
                                                                         , FILTER(Stage, not isblank(Stage[Stage_level] )) )
return
 
  CALCULATE( MIN('CALENDAR'[MonthShortName_Year]), FILTER ('CALENDAR',CALENDAR[Sort Order Year Month] = max_sort_order ) )
                     

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.

Ashish_Mathur
Super User
Super User

Hi,

Does this measre work?

Measure = coalesce([your measure],0)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

"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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.