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

[Saga]

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?

[Saga]

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!!

[lbendlin]

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.

[Saga]

@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  . 

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.

[lbendlin]

Your measure is missing the SELECTEDVALUE(Month) filter.

[Saga]

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

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 ) )

                     

[lbendlin]

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]

Hi,

Does this measre work?

Measure = coalesce([your measure],0)

[lbendlin]

"show items with no data"

[Saga]

@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. 

[lbendlin]

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