Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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 () ) )
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!
Can you upload a sample pbix file? You can upload it to any web storage such as OneDrive, dropbox etc and share the link.
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |