Thank you for sharing your knowledge. For the time being, I am spending time on other endeavors, but I look forward to the day that I will be going through these tutorial videos.
Nice precise examples of using the dates in filters!! Thanks! very well done Andrew! a suggestion please: would it be possible to have a link in the description to the full playlist, so one can easily go to the playlist from a randomly found video. yt doesn't handle this yet, guess it's a good feature to add :)
Thanks Hadi! And thanks for the suggestion about the playlist link. We do already include that link in the end screen of the video but if it will help to have it in the description as well I'll make a note to do this for future videos!
Hi Andy, I have some code that counts the number of cases that are over 'x' amount of days old that works. I have a second measure that tries to calculate the value of 'x' a month ago but it just gives me a - - result (its not a BLANK and its not empty). Would it be possible to send you the code for you to see if there are any glaring mistakes in it? If so, could I paste it here or is there another way to send you it? I have added comments to the code to help explain what's what. Thanks
Hi! You're very welcome to paste your code here. Where are you seeing the - - result? And which version of Power BI are you using? I think that card visuals used to display blanks as - - by default in earlier versions of PBI.
Hi Bret! Using the DateAdd function shifts the date range of the current filter context by the specified amount. So, the date range of the filter context for the Total row spans 1 Jan 2010 to 31 Dec 2016. If you shift that date range back 1 year it becomes 1 Jan 2009 to 31 Dec 2015 so you get the average of all Run Times between those dates. If you shift the date range back 1 quarter it becomes 1 Oct 2009 to 30 Sep 2016 so you get the average of all Run Time between those dates. They're not particularly useful values to see in this matrix, I agree! It would be better to suppress them using a function like ISINSCOPE or HASONEVALUE to only show subtotals for the relevant groups. I hope that helps!
Hi Andy, I have a Matrix that shows various Time Intelligence based figures and one of them is the change in volume of Cases Month on Month as a %. Cases MoM% is a Measure (see below) that is , as it suggests, the variance as a % on the previous month. How do I stop the Variance showing on Year sub-total level? Is there anything that can be changed in the code below that would just show the % next to the months and not the Year? Cases MoM% = VAR ActiveCase = [YTD Active Cases] VAR ActiveCasesPM = [Previous Mth] VAR Result = IF( [YTD Active Cases] 0 && [YTD Active Cases PY] 0, DIVIDE(ActiveCase - ActiveCasesPM, ActiveCasesPM) ) RETURN Result I've been looking for a solution everywhere for this and the easy way out is to change the colour of the font and essentially 'blank' the value out. I want to know how to do this through DAX. Fingers crossed its straightforward!
Hi Andy! Yeah, there are a couple of fairly straightforward things you can do here. Try something like: RETURN IF( ISINSCOPE('Your month field name'), Result ) I hope it helps!
Too bad that it isn't really Time intelligence. Should have been called Period Intelligence or maybe Date Intelligence. I wish someone would figure out haw to actually do Time Intelligence. Things like same period last hour, or Hour over Hour. That would be Time Intelligence.
Hi! Time Intelligence is Microsoft's official name for these features and functions learn.microsoft.com/en-us/dax/time-intelligence-functions-dax learn.microsoft.com/en-us/training/modules/dax-power-bi-time-intelligence/
Thank you for sharing your knowledge. For the time being, I am spending time on other endeavors, but I look forward to the day that I will be going through these tutorial videos.
You're welcome! Thanks for taking the time to leave a comment and I hope you find time to start watching soon!
Yoooo wise owl got the platinum skin 🤯🤯😳😳😳🔥🔥🔥🔥
Glad you like it 😀
Nice precise examples of using the dates in filters!! Thanks! very well done Andrew! a suggestion please: would it be possible to have a link in the description to the full playlist, so one can easily go to the playlist from a randomly found video. yt doesn't handle this yet, guess it's a good feature to add :)
Thanks Hadi! And thanks for the suggestion about the playlist link. We do already include that link in the end screen of the video but if it will help to have it in the description as well I'll make a note to do this for future videos!
Hi Andy, I have some code that counts the number of cases that are over 'x' amount of days old that works.
I have a second measure that tries to calculate the value of 'x' a month ago but it just gives me a - - result (its not a BLANK and its not empty).
Would it be possible to send you the code for you to see if there are any glaring mistakes in it? If so, could I paste it here or is there another way to send you it?
I have added comments to the code to help explain what's what.
Thanks
Hi! You're very welcome to paste your code here. Where are you seeing the - - result? And which version of Power BI are you using? I think that card visuals used to display blanks as - - by default in earlier versions of PBI.
I find the column totals confusing on previous quarter and previous year. How should they be understood ?
Hi Bret! Using the DateAdd function shifts the date range of the current filter context by the specified amount. So, the date range of the filter context for the Total row spans 1 Jan 2010 to 31 Dec 2016. If you shift that date range back 1 year it becomes 1 Jan 2009 to 31 Dec 2015 so you get the average of all Run Times between those dates. If you shift the date range back 1 quarter it becomes 1 Oct 2009 to 30 Sep 2016 so you get the average of all Run Time between those dates. They're not particularly useful values to see in this matrix, I agree! It would be better to suppress them using a function like ISINSCOPE or HASONEVALUE to only show subtotals for the relevant groups.
I hope that helps!
Hi Andy, I have a Matrix that shows various Time Intelligence based figures and one of them is the change in volume of Cases Month on Month as a %. Cases MoM% is a Measure (see below) that is , as it suggests, the variance as a % on the previous month. How do I stop the Variance showing on Year sub-total level? Is there anything that can be changed in the code below that would just show the % next to the months and not the Year?
Cases MoM% =
VAR ActiveCase = [YTD Active Cases]
VAR ActiveCasesPM = [Previous Mth]
VAR Result =
IF(
[YTD Active Cases] 0 && [YTD Active Cases PY] 0,
DIVIDE(ActiveCase - ActiveCasesPM, ActiveCasesPM)
)
RETURN
Result
I've been looking for a solution everywhere for this and the easy way out is to change the colour of the font and essentially 'blank' the value out. I want to know how to do this through DAX.
Fingers crossed its straightforward!
Hi Andy! Yeah, there are a couple of fairly straightforward things you can do here. Try something like:
RETURN IF(
ISINSCOPE('Your month field name'),
Result
)
I hope it helps!
Thanks, that works! Much appreciated Andy
@@ArchStanton806 No probs!
what does DAX stand for ?
Hi! Its Data Analysis eXpressions.
Too bad that it isn't really Time intelligence. Should have been called Period Intelligence or maybe Date Intelligence. I wish someone would figure out haw to actually do Time Intelligence. Things like same period last hour, or Hour over Hour. That would be Time Intelligence.
Hi! Time Intelligence is Microsoft's official name for these features and functions
learn.microsoft.com/en-us/dax/time-intelligence-functions-dax
learn.microsoft.com/en-us/training/modules/dax-power-bi-time-intelligence/