Forum Discussion
Quick Measure time over time comparison showing future dates
Hi All,
I'm not sure if I am the only one having this issue. I have a data that is only until March. When I created a quick measure, Power BI also added April but with zero values so this creates a -100% month over month comparision between March and April. I want my visuals to show just until March.
Hi, I am no sure what you mean.
I ended up doing this:
Value MoM% = var MaxDate = CALCULATE(max('raw data - unpivoted'[Month]),all('raw data - unpivoted'[Month])) var myValue = IF( ISFILTERED('Calendar'[Dates]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy"), VAR __PREV_MONTH = CALCULATE( 'raw data - unpivoted'[Value], DATEADD('Calendar'[Dates].[Date], -1, MONTH) ) RETURN DIVIDE('raw data - unpivoted'[Value] - __PREV_MONTH, __PREV_MONTH) ) RETURN IF( myvalue=-1,blank(),myValue )I added this
myvalue=-1,blank(),myValue
8 Replies
Hi danextian
Do you have a Master Calendar Table ?
How is it created ? Is it upto the last date of transactions data or today ?
Check it out .
Cheers
CheenuSing
hello,
My table has dates in it up to March 2017 only. I did create a relationship between it and another column from a master calendar table. The future date was already there prior to creating a relationship.
Hi danextian
Could you share the data and pbix file to check and suggest a solution.
Cheers
CheenuSing
Hi danextian
What you would need to do is to create a new measure, which will evaluate if the measure is blank and if so make the output blank otherwise display the values.
Example:
- Your current measure is called "Year on Year Comp"
- New measure would be
New Year on Year = IF(ISBLANK([Year on Year Comp]),BLANK(),[Year on Year Comp]))
- That will then not display anything for April
