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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brainscand
Frequent Visitor

Calculated Measure on count of a updated date column based on order date filter

Hello,

 

Another New user on PB and been batteling with the below for days. Our consultant also couldnt get a working solution.

 

Data set: An Order Ref, Order date, Status, Changed Date(last updated date).

 

I have created meausres on the data to work out total sales ect, but i now need to calculate a count of Orders that are in a given status based on the "Change date". Easy enough,  but i need that measure in my report that is diplayed based on the Order date but counted/ filtered to the Change date for that count.

 

So  the count of Changed date is based on the order date displayed. So if I made 10 Sales today i could have had 20 that got delivered /changed date today from other sales days.  So I have orders placed today and want to compare that to the number of ordes that also got updated today.

 

ScreenShot 1.JPG

 

The above is what the report looks like. You can see the last column is ActivChange - that column needs to display the count based on ChangeDate but reflect that count in the Order date "Row" of the report. ANY Ideas will be greatly appreciated.

Data 2.JPG

 

The basic data

Data.JPG

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @Brainscand,

 

Based on my understanding, the matrix Row group place Month and Week range values, and Values place a measure to calculate the count of Changed Date within each week range when Status is "Active". Assume there are two slicers, one contains Order Date, and the other contains Changed Date, you want the slicer Order Date can't filter the matrix visual, right?

 

You can create a calculated column to return the week for each Order Date like below:

 

Week = WEEKNUM('Table1'[OrderDate],1)

 

Then create a measure like below:

 

Measure = CALCULATE(COUNT('Table1'[ChangeDate]),FILTER('Table1','Table1'[Week]=MAX('Table1'[Week]) && 'Table1'[Status]="Active"))

 

To set the Order Date slicer can't filter the matrix, you can enable Edit Interactions under Format tab, then select the Order Date slicer, click the None button. See: https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-3-11a-create-interaction-betwee...

 

You can download the attached .pbix file to have a look. If it doesn't meet your requirement, please clarify the expected results for our test.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply appologies for late response. Unfirtuntaly not the full solution as the count of the activations Change is still showing under the Order date and not under the "Change date" that is = to the order date. I did get a working solution by bring the Activations into a seperate table on there own with only the "Change date" then joined back to the main table with that date in a Key joined( Change date to Order date).

 

So my objective was to show both a count for the orders that are in"Active" by order date and another Count by orders that were activated on that Order date. All in the same table.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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