Use PowerPivot DAX to get values within a Start and End date
Again a interesting question on my ask a questions page, keep them coming :)
Luke had a question where he wanted to get a date based calculation:
I have a table of people and a table of project assignments.
The assignments have a person, start date, end date and number of hours per week.I want to create a pivot that has the date as columns and people as rows, with the total number of assigned hours as values.
I created a table in Excel that recreates this scenario:
Next I created a new table that contains a separate date range:
I loaded both tables into PowerPivot. Because I want to get the total number of hours per day I am unable to work with a relationship between the date table and the fact table. I need to create a formula that searches the fact table for those that rows are appropriate for each date value in the pivottable.
=Calculate( sum(Project[Hours]), FILTER(Project, COUNTROWS(FILTER(VALUES(Dates[Dates]), Project[startdate] <= Dates[Dates] && Project[enddate] >= Dates[Dates] )) > 0) )
This measure does the following:
Calculate the sum of project[hours] for those rows in the Project table that has more than 0 rows in the Date table where Project[startdate] < = ‘Date’[Date] and Project[enddate] >= ‘Date’[Date].
The only downside of this query has to iterate through the entire project table for each row in the date table, is if the Project table is huge this might have big performance impact.
A second question was to create a project start date to current date sum. Again we need to create a measure to do it:
=if(COUNTROWS(VALUES(Dates[dates])) = 1, SUMX(DATESBETWEEN(Dates[dates], FIRSTDATE(Project[StartDate]), VALUES(Dates[dates])) , Project[SumPerDayMeasure]) , BLANK())
What happens here is that we want to sum all the values of the measure per day for each day from the beginning of the project to the current day.
Sumx will sum the values of our previous measure for each day between FIRSTDATE(Project[StartDate]) and VALUES(Dates[dates]), being the current day in the context.
I want to sync my last.fm with twitter: you can use the “Recently played tracks” feed and Twitterfeed. First, create (if you haven’t) an account on Twitterfeed and click on “go to my twitterfeeds (or create a new one)”, then on “Create a new feed”. On that page, enter your Twitter name and password (you can click on ‘test twitter authentication’ if you want to) and on “RSS feed url” type your “Recently played tracks” feed and click on ‘test rss feed’. On “Update frequency” and “Post up to X new updates each time”, you can choose how frequently (and how many updates) you want your tracks to be shown up, but remember to be considerate with your followers, since if you set this to update every 30 mins 5 new updates, this can be somewhat annoying to others. Then on the “Include” drop down menu, choose “title only”, so in this way, you can tweet the direct link to the song, and not only to the musician. Don’t forget to check the box next to “Include item link”. When you’re finished, click on “Update”, go listen to music and see if it works (it should be in a short time) Hope this helps you.
