Generate A Target Using Data

[myk83]

I'd like to display some targets and KPIs relating to the data I have in my report, but the visualisations always require a field to do this, and I don't have a field in my data to do that so I need to create a new one.  I want to be able to generate targets based on a constant value multiplied by some information within my data, e.g. Target = 500 * number of working days this month to date.

I've tried used a Calculated Measure to do a distinctcount of the number of days I have data for, but it returns 366 all the time, and if I do it using [Day] instead of [Date] on my date field it just gives me 31, so for some reason it's not counting my data properly.

Is it possible to use a formula to generate these kinds of targets?

I'm more used to using Crystal Reports where I know my way around the data and systems and what it can/can't do!

[Eric_Zhang]

---

@myk83 wrote:

I'd like to display some targets and KPIs relating to the data I have in my report, but the visualisations always require a field to do this, and I don't have a field in my data to do that so I need to create a new one.  I want to be able to generate targets based on a constant value multiplied by some information within my data, e.g. Target = 500 * number of working days this month to date.

I've tried used a Calculated Measure to do a distinctcount of the number of days I have data for, but it returns 366 all the time, and if I do it using [Day] instead of [Date] on my date field it just gives me 31, so for some reason it's not counting my data properly.

Is it possible to use a formula to generate these kinds of targets?

I'm more used to using Crystal Reports where I know my way around the data and systems and what it can/can't do!

---

Supposing there's a calendar table in your case. Try a measure as

Target =
500
    * CALCULATE (
        COUNTROWS ( 'calendar table' ),
        'calendar table'[isWorkingDay] = 1,
        'calendar table'[Date] <= TODAY (),
        'calendar table'[Date]
            > TODAY () - DAY ( TODAY () )
    )

[myk83]

I tried to create a new table that contained all the dates, but I'm struggling to understand it all. 

In my main query table, I created a new column which uses a formula to say that if the original Date column is a weekend, then show the Friday date instead, otherwise use the original Date.  This is called "Reported Date" column.

I created a new table and used Distinct(Query1[Reported Date].[Date]) to populate it, but for some reason this gave me 366 rows - 1 for each day of the year!

I tried to modify it and include Distinct(DatesMTD(Query1[Reported Date].[Date])) and that gave me less rows, but I still somehow managed to get a row for each day, and it was December, not this month!

[Eric_Zhang]

@myk83

Can you upload a sample pbix file? You can upload it to any web storage such as OneDrive, dropbox etc and share the link.

[ankitpatira]

@myk83 Having measure that calculates your target is the best approach for your scenario. Follow below steps to create a measure that gives you Target.

1. In your dataset create calculated column that gives you day in a week. WeekdayColumn = WEEKDAY(YOURTABLE[dateColumn],2). This will give day within a week from your dates.

2. Create second calculated column which determines whether it is working day or not. WorkingDay = IF(YOURTABLE[WeekdayColumn] > 5, 0, 1)

3. Create Target measure. Target = TOTALMTD(500 * SUM(YOURTABLE[WorkingDay]), YOURTABLE[dateColumn])