Re: Previous Month Calculation inaccurate results

gregwilliams55 · ‎03-14-2017

Hello-

Source: Project Data with Start and Finish Dates

I'm attempting to capture the amount of active projects for the previous month using a calculated measure. I have created and vetted the measure for capturing the current period it works fine. However, for the previous month measure, I'm seeing variances in what should be reported as a previous month.

As an example: August 2015 had 285 active projects (calculated with original measure and confirmed with data), however, using the previous month measure Sept 2015 is showing 277.

I should mention this is not consistent, the measure works fine for most months.

Both measures are detailed below.

Anybody have any ideas for the variance?

Original Measure to Calculate Current Period's Active Projects:
1_ActiveMACProj = 
	CALCULATE(
		SUM(MACProjFileNew___MASTER[Distinct Project Count]),
		GENERATE(
			VALUES(Dates1[date]),
			FILTER('MACProjFileNew___MASTER',
				CONTAINS(
					DATESBETWEEN(
						Dates1[Date],
						MACProjFileNew___MASTER[Created],
						MACProjFileNew___MASTER[Actual Finish Date]
					),
					[Date],Dates1[date]
				)
			)
		)
	)

Measure to Calculate Previous Month's Active Projects:
1_LM_ActiveProjects = 
	CALCULATE(
		SUM(MACProjFileNew___MASTER[Distinct Project Count]),
		GENERATE(
			VALUES(Dates1[Date]),
			FILTER('MACProjFileNew___MASTER',
				CONTAINS(
					DATESBETWEEN(
						Dates1[Date],
						MACProjFileNew___MASTER[Created],
						MACProjFileNew___MASTER[Actual Finish Date]
					),
					[Date],DATEADD(Dates1[Date],-1,MONTH)
				)
			)
		)
	)

Anonymous · ‎03-14-2017

Hey @gregwilliams55, quick question here: if you create the 1_ActiveMACProj measure, why are you recreating the DAX again and using DATEADD? I believe you could simply use the measure you created, but calculate it for the previous month:

CALCULATE ( [1_ActiveMACProj] , PREVIOUSMONTH ( Dates1[Date] ) )

I think this could make the code simpler and perhaps easier to troubleshoot. If you can test and let me know if that works or it still gives the wrong number, that will help with troubleshooting.

gregwilliams55 · ‎03-14-2017

Thanks @Anonymous,

I had tried that prior, but mysteriously it didn't work...

If you had any insight on that, I would appreciate it as well.

Greg

Sean · ‎03-14-2017

I suspect Previous Month does not work because DATEADD returns a table where a column/value is expected CONTAINS

What do you get with this version?

EDIT: BTW This is still meant to get the Current Month! For Previous Month I would do what @Anonymous proposed - works.

1_ActiveMACProj =
CALCULATE (
    SUM ( MACProjFileNew___MASTER[Distinct Project Count] ),
    GENERATE (
        MACProjFileNew___MASTER,
        CALCULATETABLE (
            VALUES ( Dates1[date] ),
            DATESBETWEEN (
                Dates1[Date],
                MACProjFileNew___MASTER[Created],
                MACProjFileNew___MASTER[Actual Finish Date]
            )
        )
    )
)

Its seems the above runs faster in DAX studio!

@OwenAugermay have something to say about this

gregwilliams55 · ‎03-14-2017

Hey @Sean-

Thanks!

Your version, indeed, works for the current month. When I use it in the following, it yields results but they are still current month?!! In other words, no difference. Pardon me if I missing something obvious; I'm a newbie.

1_LM_ActiveMACProj2 = Calculate([1_ActiveMACProjNew],PREVIOUSMONTH(Dates1[Date]))

--where 1_ActiveMACProjNew is your version

Anonymous · ‎03-15-2017

Hey @gregwilliams55 could you share your pbix file? That would really help with troubleshooting!

gregwilliams55 · ‎03-15-2017

@Anonymous - sure thing, what's the best way to share?

Anonymous · ‎03-16-2017

@gregwilliams55 if you can uploda to dropbox, google drive, or something similar and post a link here, that would be fantastic

gregwilliams55 · ‎03-16-2017

@Anonymous @Sean

Here you go!

FYI - I stripped out a bunch of the sensitive details. It's just the Project IDs and pertinent dates. You'll also notice that there are duplicate Project IDs and unique 'task ids'. This is intentional and why I'm taking the sum of the distinct project count column.

PBIX File

Thanks!

Sean · ‎03-16-2017

@gregwilliams55

I would probably create a New Summary Table

Summary Table = 
SUMMARIZE (
    GENERATE (
        MACProjFileNew___MASTER,
        CALCULATETABLE (
            VALUES ( 'Dates1'[Date] ),
            DATESBETWEEN (
                'Dates1'[Date],
                MACProjFileNew___MASTER[Created],
                MACProjFileNew___MASTER[Actual Finish Date]
            )
        )
    ),
    MACProjFileNew___MASTER[Project Number],
    MACProjFileNew___MASTER[Project Status],
    'Dates1'[Date]
)

Then all you need is these simple Measures

Projects = DISTINCTCOUNT ( 'Summary Table'[Project Number] )

Projects LM = CALCULATE ( [Projects], PREVIOUSMONTH('Summary Table'[Date].[Date]) )

Look here...

http://community.powerbi.com/t5/Desktop/Holiday-Calculation-From-Start-and-End-Date-to-quot-Aggregat...

As @KHorseman puts in one of the comments from 06-06-2016 01:51 PM

"with the table I can write very simple sum and count measures for things like total hours per week, instead of accomplishing the same with giant mutant versions of Active Count."

Hope this helps!

KHorseman · ‎03-16-2017

@SeanI think the schedule table might be obsolete now that I've seen what @v-sihou-msft did here. It eliminates the need for extra helper tables left over in the data model, and it seems to load faster on refresh in my test.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!