Count based on Due Dates in Previous Month

[Hanshans]

Hi 

I'm new to DAX and struggling with something that I imagine is very simple

I have two tables of relevance in my model. One is a Calendar/Dates Table and One is a table that pulls in a list of off SharePoint. Each entry on the list in SharePoint records a product defect and contains a due date for resolution. I need to write a DAX measure to count how many defects were due to be resolved last month (and other similar measures based on different time spans such as last quarter, next quarter.)

I'm going round in circles even though have read around and tried all sorts of things. Thanks for help in advance

Hannah

[speedramps]

Hi Hanshans

Please consider this solution ….

Create a FactDefects table with

                DefectID
                Status   = Open  or Closed

                Product ID
                Date raised

                Date due

                Date closed

Create DimDate table with

                Date

                Month Offset   ( with 0 for this month, -1 for last month and -2, for month before, etc)

                Quarter Offset ( with 0 for this quarter, -1 for last quarter and -2, for quarter before, etc)
                         (see goodle for how to create Power Bi calendar date tables)

 Create inactive date relationships

Create measures:-

Defects due = 
CALCULATE(

COUNTROWS(FactDefects),
FactDefects[Status] = “Open”,
USERELATIONSHIP(' DimDate '[Date], FactDefects [Due Date])
)

       Defects due last month = CALCULATE([Defects due],'Calendar'[Month Offset]=-1)
      
       Defects due last quarter = CALCULATE([Defects due],'Calendar'[Quarter Offset]=-1)

Hope that helps.

[Hanshans]

Thanks Speedramps

I have added those additional columns into my DimDate Table and will have a go at the creating the DAX measures. I will mark you up as the solution if it works.

Is it possible to achieve the same without additional columns in the DimDate Table and just DAX measures using the Time Intelligence functions?

I have been trying to do that and this is my formula so far but it is not working

current_month_Due = CALCULATE(COUNTROWS('NCR Tracker'),FILTER('NCR Tracker',RELATEDTABLE('DimDate')=MONTH(TODAY())))

Any thoughts?

[Anonymous]

// DimDate must be marked as a Date table
// in the model and contain consecutive
// dates that cover all years that are
// present in the model. DimDate must be
// joined to the fact table on [Date]
// and it must be a 1:* relationship with
// one-way filtering. [Measure] is the
// measure you want to calculate one
// month back.

[Measure Last Month] =
var __currentPeriod =
	VALUES( DimDate[Date] )
var __lastMonthPeriod =
	DATEADD( 
		__currentPeriod,
		-1,
		MONTH
	)
var __noOverlap =
	ISEMPTY(
		INTERSECT(
			__currentPeriod,
			__lastMonthPeriod
		)
	)
return
	// If it does not matter to you
	// that a shifted period will have
	// any intersection with the current
	// period, just remove the condition
	// under IF and leave only the measure.
	// If you do this, remove the calculation
	// of __noOverlap above as well.
	if( __noOverlap,
		calculate(
			[Measure],
			__lastMonthPeriod
		)
	)

Best

D

[Hanshans]

Hi darlove

Thank you very much for the response

I have not had a chance to try this yet but will give it a go as soon as possible and let you know how I fair 

Can I just check my understanding of your response?

So, I create 3 variables, 'Current Period', 'LastMonthPeriod' and 'no Overlap' and then use the formula

if( __noOverlap,
		calculate(
			[Measure],
			__lastMonthPeriod
		)
	)

For [Measure] you said 

[Measure] is the
// measure you want to calculate one
// month back.

so in order to count the number of items (rows) within the NCR Tracker table that have a due date in the month prior to this month (now), is my measure NOW() / TODAY()?

Many thanks in advance

[Anonymous]

Hi there.

This is your [Measure]:

COUNTROWS('NCR Tracker')

There's never a need to make any measure tied to NOW. A measure must work for ANY period of time, not only TODAY or NOW.

Please try to learn something about proper data modelling. You can start with the following:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best
D

[Hanshans]

Hi darlove

Thanks for the video links. I have watched them both and very useful, especially the second one.

I realise now that I mis-understood what you meant by 'the thing you want to measure one month back'. I wanted to measure one month back from now whereas you were referring to the the requirements of counting rows. On same page now.... 🙂

So, I have tried the measure suggested and am not getting the right result. Have have tried the measure with and without the 'noOverlap' part. 

With 'noOverlap' the measure returns the total number of NCRs in the table when the correct result for those due last month should be 1

Due Last Month =

var __currentPeriod =

    VALUES( DimDate[Date] )

var __lastMonthPeriod =

    DATEADD(

        __currentPeriod,

        -1,

        MONTH

    )

RETURN

        calculate(

            COUNTROWS('NCR Tracker'),

            __lastMonthPeriod

        )

+0

With 'noOverlap' in the formula (created as Var and used in the measure) the returned result is 0, when it should be 1.

Any ideas where I've gone wrong?

Thanks

Hannah 

[Anonymous]

Hi there.

Sorry for the late reply but there's a lot going on in my professional life right now and hence my limited time.

If your model were correctly built, the measure I gave you would have worked. NO DOUBT ABOUT IT. If it does not, then there are a couple of issues that come to my mind. But most likely you have not ensured that the DimDate table is correctly connected to 'NCR Tracker.' Your description of the issue points strongly to this problem.

Please make sure that DimDate joins to the fact table on the [DueDate] field in the latter. Also, you must create a proper date table for this to work. Please watch this to know how to deal with time-intel properly:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best

D

[Hanshans]

Hi darlove

Not a problem, thought you replied quite quickly 🙂

Think I have worked out why its not working - DimDate table is not marked as Date Table. Will do that and try again.

Hope things have settled down with you

[Hanshans]

Hi darlove

I'm keen to understand why this is not working (where I've gone wrong) so that I can learn for the future. I have alot of measures to build in PowerBi for different things and the more I understand the better.  I am probably trying to run a bit before I can walk but I need to do what the business requires and I guess that is part of how people learn by doing what they need to do.

If you have the time, I'd be grateful for your comment / thoughts.

Information about defects is held in a table called 'NCR Tracker'. There are two date columns in this table - 'Due Date' and 'Close By Date'

I have a dates table called 'Calendar Table'. This has been marked as the date table using the column 'Date', which is a consecutive lists of dates from 01 January 2017 to 01 January 2021. 

There is a one to many relationship between the Calendar Table  (One) and the NCR Tracker (Many). The relationship is between the 'Date' column in the Calendar table and the 'Due Date' column in the NCR Tracker. This relationship is active. Below is a snippet of the model

This is the calculation for the measure

Due Last Month =

var __currentPeriod =

    VALUES( 'Calendar Table'[Date] )

var __lastMonthPeriod =

    DATEADD(

        __currentPeriod,

        -1,

        MONTH

    )

var __noOverlap =

    ISEMPTY(

        INTERSECT(

            __currentPeriod,

            __lastMonthPeriod

        )

)

RETURN

if( __noOverlap,

        calculate(

            COUNTROWS('NCR Tracker'),

            __lastMonthPeriod

        )

    )

It is currently returning 'Blank' (0).  Actual answer is 3.

I cant see where I've gone wrong. I know you said that the result strongly suggested that it was due to the build of my model. Are you able to tell from the info I have provided

Many thanks in advance

[Anonymous]

Hi there.

First, you should understand that all columns in a fact table must always be hidden and slicing can only be done through dimensions.

Please watch this to know what dimensional modeling is (which you should always follow when working in PBI if you want to have an easy time):
https://www.youtube.com/watch?v=fK70mN7r1fE

Secondly, if you drop on your visual months from Calendar and then the measure, you'll get exactly what you want. If the period of time you're dropping is less than a month, say, a day, the measure will return COUNTROWS for which [Date Due] is the day minus one month. If the period of time selected is greater than 1 month, BLANK will be outputted due to the __noOverlap variable. If you remove the condition with __noOverlap, you'll be calculating COUNTROWS for the current period of time moved back 1 month with no regard to whether the two periods intersect or not.

Best
D