Forum Discussion
Months between dates
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 ...
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
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
Try adding your data (count?) as a measure and adding +0, e.g. countx(fact,[column to count])+0