Forum Discussion
Last period comparison
Hello,
Novice level - only starting to get know something about Power BI.
My task: to compare data with previous period. At the moment I have data only for year 2017 so for a start comparison with previous month would be good.
I added Date table with formula:
Date = (CALENDAR (MINX('Toggl';'Toggl'[Start date]); NOW()))
For difference I created formula: Difference compared to last month = if(isblank ([Duration last month]); blank(); [Time spent] - [Duration last month])
That includes: Duration last month = CALCULATE('Toggl'[Time spent]; PREVIOUSMONTH('Date'[Date]))
And: Time spent =
VAR DurationRUNNER =
SUM ('Toggl'[Duration])
RETURN
IF (
ROUNDDOWN ( DurationRUNNER; 0 ) > 1;
ROUNDDOWN ( DurationRUNNER; 0 ) * 24
+ HOUR ( DurationRUNNER )
& ":"
& (
MINUTE ( DurationRUNNER ) & ":"
& SECOND ( DurationRUNNER )
);
FORMAT ( DurationRUNNER; "hh:mm:ss" ))
So my problem seems to be that I had to use previous formula to sum up duration data type (and so that I would get it in format hh:mm:ss without it would remove every summed 24h from result). And with this formula my calculation type is text. And it can't be changed.
BUT my measure needs number format and gives me below error:
Example of part of my visual where I would like to add comparison (to compare e.g months 4 and 5 - if those are periods chosen to be compared by viewer of dashboard).
I have googled and googled and still stuck with my task. Really hope that you could help me with that! :)
Best regards,
Sirle
Hi Sirle,
In your scenario, you formatted the [Time spent] first that changed its data type to text, which is not supported to do mathematical calculation "subtraction": [Time spent] - [Duration last month].
As a workaround, maybe you could do subtraction first then format the return result. Generally speaking, make measure [Time spent] and [Duration last month] to return numeric values, then, add the format expression in [Difference compared to last month].
Time spent =SUM ('Toggl'[Duration])
Duration last month = CALCULATE('Toggl'[Time spent]; PREVIOUSMONTH('Date'[Date]))
numeric Difference=if(isblank ([Duration last month]); blank(); [Time spent] - [Duration last month])
format Difference = IF (
[numeric Difference]=blank();blank();
IF(
ROUNDDOWN ( [numeric Difference]; 0 ) > 1;
ROUNDDOWN ( [numeric Difference]; 0 ) * 24
+ [numeric Difference]
& ":"
& (
MINUTE ( [numeric Difference] ) & ":"
& SECOND ( [numeric Difference] )
);
FORMAT ( [numeric Difference]; "hh:mm:ss" )))Regards,
Yuliana Gu
2 Replies
So I understand that my actual problem is variable that finally helped to get the data format we wanted for summing up duration data. :(
I tried if I at least get it to work when I create duplicate column of my duration data and transform it to decimal format (which isn't exactly what I want but at least something).
Made new measures with this Duration2.
Result: I got my visual without error but "duration last month" doesn't give me any results on visual. Just an empty visual. For me it seems even crappier - I didn't get what I wanted and don't get any hint of what may be wrong :D
Used this measure: Duration last month = CALCULATE(sum(Toggl[Duration2]); PREVIOUSMONTH('Date'[Date]))
v-yulgu-msftMicrosoft Employee
Hi Sirle,
In your scenario, you formatted the [Time spent] first that changed its data type to text, which is not supported to do mathematical calculation "subtraction": [Time spent] - [Duration last month].
As a workaround, maybe you could do subtraction first then format the return result. Generally speaking, make measure [Time spent] and [Duration last month] to return numeric values, then, add the format expression in [Difference compared to last month].
Time spent =SUM ('Toggl'[Duration])
Duration last month = CALCULATE('Toggl'[Time spent]; PREVIOUSMONTH('Date'[Date]))
numeric Difference=if(isblank ([Duration last month]); blank(); [Time spent] - [Duration last month])
format Difference = IF (
[numeric Difference]=blank();blank();
IF(
ROUNDDOWN ( [numeric Difference]; 0 ) > 1;
ROUNDDOWN ( [numeric Difference]; 0 ) * 24
+ [numeric Difference]
& ":"
& (
MINUTE ( [numeric Difference] ) & ":"
& SECOND ( [numeric Difference] )
);
FORMAT ( [numeric Difference]; "hh:mm:ss" )))Regards,
Yuliana Gu