Help with DAX formulas and poorly collected data

[doitypeithere]

Thanks for clicking! I've searched the forum for similar issues and came up with nada. A few posts are close and I feel like this problems should be solvable, but I've been banging my head against a wall for three days on this and (surprise!) I'm getting quite frustrated with Powerpivot. I've been using PP off and on for the last two years and have about a dozen successful, albeit simple, reporting projects under my belt. I'm no expert by any means, but I feel like I at least have a clue with what I'm about . After this week, I'm not so sure anymore.

The situation
I'm working on a reporting project that pulls customer data from an external solution I cannot change. I'm crunching the data in Excel/PowerPivot 2013. In my external source, the data is being collected as rows with the following relevant columns:

[Program Name] - [Program Start Date] - [Program End Date] - [Total $ Spent on Program]

My task is to break the [Total $$$ Spend on Program] field into a [Daily $ Spent on Program] number and then roll it up into fiscal quarters and fiscal years.

The somewhat successful solution

• I have a typical 'Dates' table with fiscal quarters and fiscal years and a relationship created between the [Program Start Date] and the [Date Key].
• I've written calculated columns that use my 'Dates' table to count the applicable rows between [Program Start Date] and [Program End Date] and divides the [Total $ Spent on Program] by that calculated number of days to get a [Daily $ Spent on Program] value. Then more calculated columns that multiply the [Daily $ Spend on Program] by a count of days in the program that occur in each fiscal quarter and year.

The calculated columns & formulas look something like this:

[Program Duration] = calculate(countrows('Dates'), datesbetween('dates'[DateKey],[Program Start Date], [Program End Date]))
[Daily $ Spend on Program] = [Total $ Spent on Program]/[Program Duration]
[Q1FY15 $ Spent on Program] = [Daily $ Spend on Program]*(calculate(countrows('Dates'),datesbetween('dates'[DateKey],[ProgramStart],[ProgramEnd]), 'dates'[FiscalYear]=2015), 'dates'[FiscalQuarter]=1)
...and so on for each fiscal quarter/year

In the pivot tables, I have simple measures that sum up each [QxFYyy $ Spend on Program] column to give me the total FY aggregations. [Financial Year] and [Financial Quarter] slicers work fine, and the aggregation measures work fine.

The problem
The above solution works, but I need to plan for the long term. Expecting someone to come behind me a write in 4 new calculated columns every year and then write new measures isn't appealing to me or to my client. I feel like I should be able to write a measure(s) that aggregates the [Daily $ Spent on Program] column in such a way that I can slice the data properly using the [Fiscal Quarter] and [Fiscal Year] columns from my dates table, without needing all those stupid calculated columns. But, all of the measures I've written have the same problem: programs with [Program Start Date] and [Program End Date] that are both within a single quarter work great, but any program that spans a quarter (or even worse, and entire fiscal year) aren't aggregated correctly. Most of the results I've gotten are that programs are always "counted" in the quarter that contains the [Program Start Date] field. Even when I tried breaking the relationship between the 'dates' table and the 'data' table and using a disconnected slicer!

Sample data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Program Name[/TD]
[TD="align: center"]Program Start Date[/TD]
[TD="align: center"]Program End Date[/TD]
[TD="align: center"]Total $ Spent on Program[/TD]
[/TR]
[TR]
[TD="align: center"]Program 1[/TD]
[TD="align: center"]3/1/2015[/TD]
[TD="align: center"]5/31/15[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]Program 2[/TD]
[TD="align: center"]3/1/2015[/TD]
[TD="align: center"]7/10/15[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]Program 3[/TD]
[TD="align: center"]3/1/2015[/TD]
[TD="align: center"]4/30/16[/TD]
[TD="align: center"]100[/TD]
[/TR]
</tbody>[/TABLE]

Expected Result
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Program Name[/TD]
[TD="align: center"]Q1[/TD]
[TD="align: center"]Q2[/TD]
[TD="align: center"]Q3[/TD]
[TD="align: center"]Q4[/TD]
[TD="align: center"]2015 Total[/TD]
[TD="align: center"]Q1[/TD]
[TD="align: center"]2016 Total[/TD]
[/TR]
[TR]
[TD="align: center"]Program 1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Program 2[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Program 3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]280[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[/TR]
</tbody>[/TABLE]


If anyone has any ideas or guidance on how this might be accomplished, I'd be eternally grateful! I'm driving myself crazy on this one.

Thanks!

 

[scottsen]

Before I forget, do a google search on "dax events in progress". It's not *exactly* the same problem, but it is similiar enough that it might inspire you -- and there is a ton of work into that problem.

First question: Why bother with this?

[Program Duration] = calculate(countrows('Dates'), datesbetween('dates'[DateKey],[Program Start Date], [Program End Date]))

I mean, it is fine, but so is [Program End Date]-[Program Start Date] (subtracting 2 dates gives you the number of days).
If you have interesting holidays/weekends/etc to account for... maybe there is a reasonable path there, but honestly... given the way your table is layed out... I kinda doubt it.

I would make a simplifying calculated COLUMN that is just the "Program Spend Per Day". I can't wrap my head around why you would want that figure to ever change (by slicer/filter/etc) -- it "feels" like a fixed cost to me.

The next thing is that I really don't think a your fact table should be related to your calendar table. Cuz... which one do you pick? the start or the end? You will see in the "events in progress" pattern that you typically do NOT create a relationship. You just do all the magic in your dax measure.

It's a bit late at close to midnight for me to give more specific answers, but ... if that doesn't get you going, come back -- and maybe post a sample workbook, and I will get you going.

 

[ImkeF]

Hi, take the following steps - DimDate should be disconnected:

1) Create measure from the calc column:

Duration:=SUM([ProgramDuration])

2) Calculate how the hours should be allocated:

    DaysAllocated:=DIVIDE(CALCULATE([Duration], FILTER(ProgramDates; [ProgramStartDate]<=MAX(DimDate[Datum]) && [ProgramEndeDate] >=MAX(DimDate[Datum]))),(MAX([ProgramEndeDate])-MAX([ProgramStartDate])))

3) Apply that by iterating over both tables:

Days:=SUMX(VALUES(DimDate[Datum]),SUMX(VALUES(ProgramDates[ProgramName]),[DaysAllocated]))

Imke

 

[doitypeithere]

Before I forget, do a google search on "dax events in progress". It's not *exactly* the same problem, but it is similiar enough that it might inspire you -- and there is a ton of work into that problem.

This was seriously helpful. Thank you! I still have a strange problem, so maybe I overlooked something. See below.

First question: Why bother with this?

[Program Duration] = calculate(countrows('Dates'), datesbetween('dates'[DateKey],[Program Start Date], [Program End Date]))

I mean, it is fine, but so is [Program End Date]-[Program Start Date] (subtracting 2 dates gives you the number of days).
If you have interesting holidays/weekends/etc to account for... maybe there is a reasonable path there, but honestly... given the way your table is layed out... I kinda doubt it.

I would make a simplifying calculated COLUMN that is just the "Program Spend Per Day". I can't wrap my head around why you would want that figure to ever change (by slicer/filter/etc) -- it "feels" like a fixed cost to me.

You are absolutely correct. The first iteration of the report did calculate the program duration by subtracting the dates. I've been over and over this so many times that I may have lost track of exactly why I did what. I think I wrote that calculation with the datesbetween() function as an experiment to see if the filter context carried over into the measure. It didn't of course, but it still returned the right values and didn't impact performance, so I never changed it back.

The next thing is that I really don't think a your fact table should be related to your calendar table. Cuz... which one do you pick? the start or the end? You will see in the "events in progress" pattern that you typically do NOT create a relationship. You just do all the magic in your dax measure.

It's a bit late at close to midnight for me to give more specific answers, but ... if that doesn't get you going, come back -- and maybe post a sample workbook, and I will get you going.

Ok, so here goes. I boned up on as many articles and whitepapers as I could find on "dax events in progress" and I think I've made a lot of progress. I have a measure that is returning correct values at the row level when filtered with a slicer, but the aggregations aren't quite correct. They are maddeningly close to being right! I've been using DAX Studio to double check that the generated table is right, and as far as I can tell it's being created properly. I'm pretty sure my mistake is in the SUMX() function, but I can't quite figure out what I've done wrong.

DailySpendTEST=calculate(sumx(filter(generate(summarize(ProgDetails,ProgDetails[Program Name],ProgDetails[ProgramStart], ProgDetails[ProgramEnd], ProgDetails[DailySpend]), DATESBETWEEN(Dates[DateKey], ProgDetails[ProgramStart], ProgDetails[ProgramEnd])), contains(values(Dates[DateKey]), [DateKey],Dates[DateKey])),[DailySpend]))

Here is a sample workbook with a couple of thousand rows of data, the measure above, and the results of my calculated column-based solution that I know is correct. I appreciate your help so far. I feel like I've added another tool into my arsenal, which is always a nice feeling.

 

[ImkeF]

Wow - that's impressive! - just iterate on ID-level instead on program name

DailySpendTEST=calculate(sumx(filter(generate(summarize(ProgDetails,ProgDetails[[B][U][/U][I]ID[/I][/B]],ProgDetails[ProgramStart], ProgDetails[ProgramEnd], ProgDetails[DailySpend]), DATESBETWEEN(Dates[DateKey], ProgDetails[ProgramStart], ProgDetails[ProgramEnd])), contains(values(Dates[DateKey]), [DateKey],Dates[DateKey])),[DailySpend]))

Imke

 

[doitypeithere]

Wow - that's impressive! - just iterate on ID-level instead on program name

DailySpendTEST=calculate(sumx(filter(generate(summarize(ProgDetails,ProgDetails[[B][I]ID[/I][/B]],ProgDetails[ProgramStart], ProgDetails[ProgramEnd], ProgDetails[DailySpend]), DATESBETWEEN(Dates[DateKey], ProgDetails[ProgramStart], ProgDetails[ProgramEnd])), contains(values(Dates[DateKey]), [DateKey],Dates[DateKey])),[DailySpend]))

Imke

That did the trick! I'm actually a little miffed at myself for missing that. Thank you!

 

[scottsen]

I kinda feel like we need a 3 way high five or something