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 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 ?
6 Replies
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-daxhalfglassdarkly 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 * cardinalityTry adding your data (count?) as a measure and adding +0, e.g. countx(fact,[column to count])+0
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.
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 * cardinalityMeasures do not belong to any table. They can be stored anywhere. Also, this measure simply has to be dragged to your matrix/table visual.
