Count of Sales excluding certain product measure

DAX DAX Calculations
1

Hi there,

I’m trying to create a measure that will show the count of purchases excluding a certain product (in this case it’s the wine Riesling) between a certain period in this case the table is called FY period and it is written as 202403 for March 2024, 202305 for May 2024 etc.

However the number doesn’t seem to be accurate when I drag the measure into a card.

Can anybody help me to see where I am going wrong with my measure?

The measure code is below:

Sales excluding Riesling between March and May =
CALCULATE(
COUNTROWS(‘Module Sales with Inventory’),
EXCEPT(
ALL(‘Module Sales with Inventory’),
FILTER(
‘Module Sales with Inventory’,
RELATED(‘Dimension Item’[description]) = “Riesling”
)
),
FILTER(
‘Dimension Period’,
VALUE(‘Dimension Period’[fyperiod]) >= 202303 && VALUE(‘Dimension Period’[fyperiod]) <= 202305
)
)

2

Hi @Greg_B,

Welcome to Forum.

You can check Data Mentor Tools Advisor response available at:

3

hello

can you attach a sample file of you pbix please so we can see the result of this measure

kind regards
Roger

4

Hi Roger,

Unfortunately I am unable to share the PBIX file as it contains sensitive information.

I have create sample data and put them at the bottom here if that would help?

Each field I want to use in the measure come from different tables.

Hopefully you’d be able to show me the correct way to implement them into the measure so I can solve this query?

FYPeriod Table
FY Period
20240301
20240302
20240303
20240304
20240305
20240306
20240307
20240308
20240309
20240310
20240311
20240312
20240313
20240314
20240315
20240316
20240317
20240318
20240319

Sales Table
Amount
100
123
176
154
112
109

Item Table
Description
Sauv Blanc
Riesling
Chardonnay
Pinot

Customer Table
Customer Name
Jenny
Conor
Leo
Peter
James
Mark

5

I love the report from Data Mentor that @mspanic did. Its powerfully tool that EDNA team has working on to help us.

Did you try an AI tool like Data Mentor or ChatGPT to help you?

Attaching a proper phix file with SAMPLE Data within would definitely help people of the forum where you are getting stuck. Maybe your structure on how your file is setup.

Just from the coding the following questions I would have:

  1. Are you using the EDNA Date Table? Here is the link to it that @Melissa have created for the forum to use. Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

  2. Do you have the Date Table marked as Date Table.

  3. I wouldn’t do any kind of hard coding within your DAX of dates but instead use a two date slicer and reference that date slicer (beginning slicer and Ending date slicer). You would have to change the coding every time if you want a different kind of period.

  4. Here is a video that @BrianJ did for the forum to help mask Sensitive Data that you can provide the forum with with a PHIX file.
    TIP: Tools and Techniques for Providing PBIX Files with Your Forum Questions - Forum Guides - Enterprise DNA Forum

I hope this helps.
thanks
Keith

6

Greg

this info is not very helpfull? how are the relationships and does the sales table have only 6 entries and only one column?

kind regards

Roger

7

Hi Roger,

Apologies, I’ve created a PBIX with sample data based on the same tables and fields I’m trying to use.

Please find the link to the Google Drive here - https://drive.google.com/file/d/1oAMGapVUty-exSWIfGtDok1XmsI54E3R/view?usp=drive_link2

Many Thanks

8

Greg,

can you upload the pbix using the upload arrow above? The google link doesn’t work properly to get the pbix file
kind regards
roger

9

GBARR_Test_Data.pbix (81.9 KB)
I’ve uploaded now. Please see if that works.

10

Gregg

GBARR versions 1.pbix (300.9 KB)

attached a pbix file with some measures, I added melissa dateTable, there is a column that matches your FYyyyymm column
There is a table check table for measures just for check purpose, you can delet it

I 'm not sure if this is what you are looking for !?

kind regards
Roger