The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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) ) ) ) )
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.
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
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
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
Hey @gregwilliams55 could you share your pbix file? That would really help with troubleshooting!
@Anonymous - sure thing, what's the best way to share?
@gregwilliams55 if you can uploda to dropbox, google drive, or something similar and post a link here, that would be fantastic
@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.
Thanks!
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...
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!
@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.
Proud to be a Super User!
You are absolutely right!
In fact I just remembered seeing this in a video from @MarcelBeug
http://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/m-p/129453#U129453
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |