cancel
Showing results 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

Community Champion

## KPIs using None Standard date Periods

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?

12 REPLIES 12
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

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)
Solution Sage

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

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

Solution Sage

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
•

•

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

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

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

Solution Sage

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

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

Solution Sage

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

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

Solution Sage

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.