Hello, I am faily new to Power tools such as Pivot/BI etc. I just wonderd if anyone could help with this one;
I have a relatively large data set that for some reason or another is missing data for the year "2011" - I generated my date table, which has generated dates for the missing period. How can I handle this?
I tried using the "Allnoblankrows" function when trying to calcuate standard sum measure as I assumed that this function would exlcude blank rows.
Slicer is also a pain as it shows year (I use the date table to generate the slicer as standard practice).
There is the obvious option of simply deleting 2011 dates from date table, but I don;t want to do this, I just would like to try and handle this situation.
I created orignal sum measure with the allnoblankrows i.e. x=calculate(data[field_x],allnonblankrows(Calender[date])).
My assumption was that this measure (structure) would trickle down into other measures.
Possibly thinking too hard about this or not enough... like to do things properly...any help here much appreciated.
Very easy to follow with PowerQuery and customised as required, thank you for this - really helpful.
I confess I am still curious about handling orginal problem with DAX - this must happen so often surely there has to be a way of using a function along the lines of Allnoblankrows, handling slicers too.. I feel it's the only way I am going to learn.