Months between dates

[Saga]

Hello,

I have a calendar table [Table 1] with dates from 2015 to 2030 which has dates, month, month_year, year etc

I have another table [Table 2] which has facts. This fact table has 2 measures which gives me the min date and max date depending on my slicer selection value.

Each time slicer selection change,  i will have a new min and max date.

Now i want to get all the month_year between the min and max measures including the min and max date months. I want the months even if there is not data or $ amounts.

Lets say the min date is Dec 2015 and max date is Jun 2016.  I want to see apr and may even if they dont have data.

If i try to use the column from calendar table month_year and try the option "show items with no data", i basically get all months from 2015 to 2030, which is not what i want. 

Any suggestions how i can get all the months between the measure dates ?

[Ashish_Mathur]

Hi,

Ensure that to the slicer and matrix visual, you drag Year and Month from the Calendar Table.  Write this measure

Total = sum(Data[Amount])

Hope this helps.

[Saga]

@Ashish_Mathur where does this measure go,I mean which table? If i use this measure to filter out the visual and slicer by using is not blank, i will not get the apr and may 16. Am i missing something here?
Just for context, my Calendar table is filtering Facts table with 1 to * cardinality

[Ashish_Mathur]

Measures do not belong to any table.  They can be stored anywhere.  Also, this measure simply has to be dragged to your matrix/table visual.

[halfglassdarkly]

If you're using the date from your date dimension in your slicer (rather than your fact date) you can create a measure to use as a filter

FilterMeasure= if([EndDate]<=[MaxinFact],"Include","Exclude")

Where [End Date] is a measure storing your max 'DateDim'[Date] from your slicer selection, and [MaxinFact] is a measure storing your maximum date in your fact table. Add the measure as a filter on your date slicer filtered to "Include".

Alternatively if your date dimension is calculated dynamically you could filter this to exclude dates beyond those in your fact table, see https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables#generate-with-dax

[Saga]

@halfglassdarkly So i have created 2 measure on my calendar table to get min and max dates from calendar. I created the suggested measure 

Months to include = if ([F_mindate] >= [Cal_mindate] || [Cal_maxdate] <= [F_maxdate], "Y", "N"), since i want starting only from Dec 15. Iam still getting the month_year like before, dynamically only the months which has  $ amounts and not the blanks months in between like the Apr and May 16.  Just for context, my Calendar table is filtering Facts table with 1 to * cardinality

[halfglassdarkly]

Try adding your data (count?) as a measure and adding +0, e.g. countx(fact,[column to count])+0