Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DebbieE
Community Champion
Community Champion

KPIs using None Standard date Periods

say you have the following KPI based on a Monthly report

KPI.JPG

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.

KPIIncorrect.JPG

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?

12 REPLIES 12
kentyler
Solution Sage
Solution Sage

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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)

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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

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

   
TermDefinition
datesA column that contains dates.
start_dateA date expression.
number_of_intervalsAn integer that specifies the number of intervals to add to or subtract from the dates.
intervalThe 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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


DebbieE
Community Champion
Community Champion

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.