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
)
)
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:
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.
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 !?
I tried one of your measures and am getting the following error below.
Please see the code I used and the error in the word doc attached.
Any idea how to fix this?
Sauv Blanc sold in April =
VAR _Item = SELECTEDVALUE( ‘Module Sales with Inventory’[Item])
var _Period = SELECTEDVALUE( ‘Module Sales with Inventory’[period])
VAR Sales_Periods =
VALUES(‘Module Sales with Inventory’[period] ) //Gets all the periods from the sales table
VAR VirtualTable =
FILTER(
SUMMARIZE(
‘Module Sales with Inventory’,
‘Module Sales with Inventory’[period],
‘Module Sales with Inventory’[Item],
‘Dimension Item’[description]
),
‘Module Sales with Inventory’[item] = “19AU1000”
|| ‘Module Sales with Inventory’[period] = 202410 // Period 202410 (April 2024 in FY Terms) and only Sauvignon blanc
)
RETURN
COUNTROWS(VirtualTable)
I’m going to add my two cents into this. If you start using Melissa date table to link up to your sales table. You won’t have to hard code anything in your dax. You can use a date slicer. Roger add the date table to your files for your use. I believe if you use the EDNA Date table, your error will not likely occur.
Did you try using the Data Mentor that EDNA developed?
There is also several youtube/course video on how to use the EDNA Date Table.
the error message says you are comparing text with a value which is not allowed.
check the column where you compare your periods, in the Datetable, I added, this is a value.
in your dimension period table check the data type and change it to a value if needed.