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
MiteshY
Frequent Visitor

QTR TARGET, PRESENT WEEK, LAST WEEK

Hello All,

 

My dashboard gets updated daily. I want to show present week, last week data, and in present, last week data i need to show QTD , QTR values along with Target. 

 

All is working fine for current week. But for last week i need a help to create a dynamic target measure, so when present week is there and quarter has ended, in last week it should show previous quarter target values.

 

For eg. Suppose today is fiscal week 14 and previous fiscal week was 13.

 

As quarter has now ended in 13th week , so in previous week it should show values till 13 week and previous quarter. But for current it will be showing quarter 2 and 14th week data.

 

NOTE- I GET QUARTELY GOALS ON QUARTER BASIS, AND FOR OTHER , DATA IS UPDATED DAILY.

 

Thanks.

2 REPLIES 2
Adamboer
Responsive Resident
Responsive Resident

To create a dynamic target measure for last week, you can use a combination of the IF and DATEDIFF functions in DAX. Here's an example of the formula you can use:

Last Week Target = IF( DATEDIFF( MAX('Date'[Date]), MAX('Table'[Date]), WEEK ) = 1, MAX('Table'[Current Week Target]), MAX('Table'[Previous Quarter Target]) )

In this formula, 'Date' is the table that contains the dates, and 'Table' is the table that contains the data you want to show. You'll need to replace these with the actual names of your tables.

The DATEDIFF function calculates the number of weeks between the current week and the week of the data in the 'Table' table. If this number is equal to 1, it means the data is from last week, so the formula uses the current week target. Otherwise, it means the data is from a previous week, so the formula uses the previous quarter target.

You can then use this measure in your visualizations to show the target for last week.

Adamboer
Responsive Resident
Responsive Resident

To create a dynamic target measure for last week, you can use the following DAX formula:

Last Week Target = IF(MAX('Table'[Week])=MAX('Table'[Current Week]), [Current Quarter Target], CALCULATE(MAX('Table'[Target]), FILTER('Table', 'Table'[Week] = MAX('Table'[Last Week]))))

In this formula, we first check if the maximum value of the 'Week' column is the same as the maximum value of the 'Current Week' column. If it is, then we use the 'Current Quarter Target' measure. If not, we use the 'CALCULATE' function to retrieve the maximum value of the 'Target' column for the last week.

To show values till the previous quarter for the last week, you can modify the formula to use the 'IF' function again to check if the last week is in the previous quarter. If it is, then you can retrieve the target values for the previous quarter using a separate measure.

I hope this helps!

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.