DAX Measure to Count rows that have in date table that match dates in fact table.

[Dicken]

I would like to count the number of month in a Year / Month Pivot table that have sales,

so if all months have sales = 12 etc.

I have managed to do this by using the date column and the built in hierarchy in the fact table ;

Please Login or Register  to view this content.

and then ;

Please Login or Register  to view this content.

Which works, surprisingly, when used in a pivot table using the Calendar / date table years and months.


Is there way to get this result with creating all the extra columns in the sales / fact table as these only existed creating the pivot using the date hierarchy.

I've attached both showing both using Dates from date table and dates from fact table.

Richard.

[romperstomper]

Similar to your last post:

Please Login or Register  to view this content.

[Dicken]

I just get an error ; syntax incorrect ;

Please Login or Register  to view this content.

I don't have a month column in Table1 , just dates, I had them in the upload as they were created along with the first pivot,
but that's what I'm trying to avoid.
I thought I might be able to filter the Calendar table to just the dates in the Table1 Date column

Please Login or Register  to view this content.

But this does not work.

RD

[romperstomper]

You can use:

=if(isblank([Tunits]),blank(),COUNTX(ALL('Calendar'[Month]),[Tunits]))

as long as you use the date fields from the calendar table in the pivot.

[Dicken]

Thanks, works a treat, I was along the right lines earlier but I don't think I'd used the BLANK() , I'd give you a rep but I it won't let me,