KPIs using None Standard date Periods

[DebbieE]

say you have the following KPI based on a Monthly report

Indicator: Fact

Target Goal: Last Month fact

Trend Axis Date (Month from the data hierarchy)

But I also have reports based on Time periods that arent Power BI Stanndard so what I have done is add a flag to the time dimension

for example Last 12 months (I have DAX that creates a flag against the last 12 months of dates

Rolling Quarter (Based on business logic) again Ive added a flag agaainst these specific dates.

If I try and create a KPI in the same way for these I cant because Trend Axis isnt a date.

Indicator: Fact

Target Goal: Last Month fact

Trend Axis: Rolling 12 months flag

is there anyway I can show these off in exactly the same way using a KPI?

[kentyler]

This may have to do with how you created your custom time periods. Can you give us an example of how you "set a flag". Thanks

[DebbieE]

This is my Flag for the Rolling 12 Months

Rolling 12 Month Flag = IF(DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)<=12 && DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)>=0 ,1,0)

And this is the one for Rolling Quarter

Rolling Quarter Flag = IF(DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)<=3 && DATEDIFF('dim Date'[date].[Date],TODAY(),MONTH)>0,1,0)

[kentyler]

It looks like the KPI indicator expects a set of dates. Perhaps you need a measure that selected the dates that have the correct flags....and then create the KPI over that.

It would help me if you could post a small sample power bi file.

[DebbieE]

Unfortunately this is embedded in a large file for a customer which I cant share

I always throught for the KPI you had to use date information from a hierarchy for this, so it makes sense that it cant use my flag? But I dont know what to do instead

[kentyler]

Sometimes when I'm blocked I try and do things a different way. Maybe instead of using your flags you can use a function like 

DATESINPERIOD

• 12/09/2018
• 2 minutes to read
•  

Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

Syntax

DAXCopy

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)  

Parameters

   

Term	Definition
dates	A column that contains dates.
start_date	A date expression.
number_of_intervals	An integer that specifies the number of intervals to add to or subtract from the dates.
interval	The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month, day

to develop your non-standard periods... and then you should have the date value to put in your KPI

[DebbieE]

Im wondering if I can create a date table with the Min date from Dim date where Rolling 12 month flag is 1 and the end date is Max Date where Rolling 12 Months flag is 1?

Im not sure how to go about it though, or how that will help me with the KPI at the moment

Also If I create a date table will that remove all the date hierarchies from my dates in DimDate? I dont want that to happen

[kentyler]

A date table is usually recommended for all power bi projects. It would replace, rather than removing your current date hierarchies. There are a lot of issues. Would you like to do a screen share and talk through them. Send me a convenient time and date in an email (ken@8thfold.com) and i'll send you a meeting invitation.

[DebbieE]

I already have a date table dim date which is my date table and I dont want to replace it with another one just for this issue.

Thank you so much. Ill see if I can get some time tomorrow. That would be great

[kentyler]

Just let me know. I'm on PST and I'm available after 8:00 am

[DebbieE]

thanks for the offer. 

Im going to have to rethink this, with it being a customer report I cant really show it so I would have to set up another test report which may take a while.

I genuinely think the answer is that the KPI must include something from a date hierarchy so if you dont use standard date periods you cant base a KPI on it.  which is a real shame

[kentyler]

You could include another calendar table, linked to the data you want to draw the KPI from. That would allow you another date hierarchy

[DebbieE]

How though

How could I create another date table using the flags in Dim date I have already set up?

date                  flag

01/01/2010      1

02/01/2010      1

03/01/2010      1

04/01/2010      1

Take date Between Min date and Maz date Where flag = 1