# Dax Formula for Multiple Conditions and Types with different data types

Hello All,

This is a list of products, I am looking to for advice on a dax formula that will check the following columns, QOH >0, Last Sale Date 4 years before TODAY(), Last Purchase Date 4 years before Today(). Then i want to multiple the QOH of the qualifying part numbers x average cost to get a dollar value of dead inventory.

I wonder if calculate table would be the best way to go? also how do you formulize today-4 years so the table will keep catching new numbers

Be nice to see a Video from Sam on some Inventory Management DAX.

thanks
Jeff D

Hi @JDopson.

Please define all acronyms for the forum members. Iām taking a stab at QOH being quantity on hand ā¦

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

• Your work-in-progress PBIX file, using sanitized data if necessary
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.

Greg

1 Like

Greg
I did supply the PBIX File did it not come through? Also, QOH is quantity on hand. Here they Are again
Data Model.xlsx (372.2 KB)
Data Model Testing.pbix (23.1 MB)

Hi @JDopson ,

See if the below helps you. Iāve assumed itās the Item Master File dataset that youāre using, feel free to tweak as need.

Measure for QOH x Avg Cost:

QOH x Avg. Cost = SUM( āItem Master Fileā[Average Material Cost] ) * SUM( āItem Master Fileā[QOH] )

Measure for the Filtered QOH x Avg Cost:

Filtered QOH x Avg. Cost =
VAR FourYearsAgo = DATE( YEAR(TODAY()) - 4, MONTH(TODAY()), DAY(TODAY()))
RETURN
CALCULATE(
[QOH x Avg. Cost],
āItem Master Fileā[QOH] > 0,
āItem Master Fileā[Last Sale Date] < FourYearsAgo,
āItem Master Fileā[Last Puchase Date] < FourYearsAgo )

Data Model Testing.pbix (23.1 MB)

1 Like

When i try an open the file it , it says my version cant open it? I checked in the MS Store, there are no updates to download. So confusing

new version of MS store when I click on get updates
You have to go to Library and click on get updates for updated apps

Thanks
Keith

Hi @JDopson ,

I took the freedom for a different approach, which is based upon the āItem Branch Recordā-table.
PBIX attached:
Data Model Obsolete Stock.pbix (23.1 MB)

The request is calculating the value of non-sold and non-purchased stock elder then four years, this on a daily basis.

The chosen workout is as follows:

1. Calculate the value of all stock at cost price

QOH at Cost price TOTAL = SUMX( āItem Branch Recordā, āItem Branch Recordā[Last Cost] * āItem Branch Recordā[QOH] )

2. Calculate the value of stock purchased and sold more or equal of 4 years ago , whereby I made the period dynamic with a slicer in quarters ( range from 4 to 20, from 1 to 5 years backwards)

``````QOH at Cost price BEFORE inactive period =
Var InActQ = [Inactive-Quarters]
Var InActYrs = ROUNDDOWN( InActQ /4, 0)
Var InActMnth = InActQ - InActYrs * 4
VAR InactivePeriod = DATE( YEAR(TODAY()) - InActYrs, MONTH(TODAY() - InActMnth), DAY(TODAY()))
Var QOHbefore = CALCULATE(
[QOH at Cost price TOTAL],
'Item Branch Record'[QOH] > 0,
'Item Branch Record'[Last Sale Date] < Inactiveperiod,
'Item Branch Record'[Last Puchase Date] < InactivePeriod )
RETURN QOHbefore`
``````
3. Calculate the value of stock sold/purchased till 4 years ago, which should be excluded, by measure branching.

QOH at Cost price AFTER inactive period = [QOH at Cost price TOTAL] - [QOH at Cost price BEFORE inactive period]

4. The what I call āobsolete stockā can be calculated only as when the total stock value is equal to the old stock (nothing sold in newer periods)

Obsolete Stock (lines, non total) = IF( [QOH at Cost price TOTAL] = [QOH at Cost price BEFORE inactive period], [QOH at Cost price BEFORE inactive period],0)

` `

Give me your thoughts, hope the source taken is correct.

Kind regards, JW

Indeed and it states updated yesterday, no new updates out there. When I click on the link it brings me to the Power Bi website.

This is a little more complicated than I need. i added a calculated column that gave me Current Inventory Value = QOH * Avg Cost. Then using Jaimeās Variable FourYearsAgo. i wrote the following formula

Var FourYearsAgo = DATE(year(TODAY())-4,MONTH(TODAY()),day(TODAY()))

return

Var FourYearsAgo = DATE(year(TODAY())-4,MONTH(TODAY()),day(TODAY()))

return

CALCULATE(Sum(āItem Master Filerā[Current Inv Value]),āItem Branch Recordā[QOH]>0,āItem Master Fileā[Last Sale Date]<=FourYearsAgo,āItem Master Fileā[Last Purchase Date]<=FourYearsAgo)`

Does this look correct?

I get this all the time

Hi @JDopson

The formula works fine, it gives you the inventory value of stock elder then 4 years (, regardless of it is selling/purchased nowadays).

I focused also on the part of your question, that the last sales and purchase of a particular product-item has to be at least 4 years backwards, that is the part not solved with your formula. (to clean up the product line of product not sold for 4 years)

Hopefully it answers your question and obviously you decide what information you want.

Kind regards, JW

If you are using the MS Store version, once you click get updates in MS Store it should update the version on your pc.

open up power bi with no file and see what version you have.
Try rebooting power bi/pc

see if that helps.
Keith

Iām on the October version still as I havenāt installed the November version if that helps - other than that Iām not sure sorry.

The Variable Jamie created should take care of that.

`VAR FourYearsAgo = DATE( YEAR(TODAY()) - 4, MONTH(TODAY()), DAY(TODAY()))`

I encountered a problem though, we have a couple different locations. some of these branches are listed as numbers and some as text. I tried to add a a filter to the calculation to only select items in Branch 1 but power bi didnāt like that. The Branch Number Column is currently formatted as text

yes i could open your file, thanks

could you post what version yours is, so i can compare to mine?

Hereās my version:

Mine says May 2021!

Clearly a problem here

Hi @JDopson

As far as I know, you can not use very much filter-conditions in one DAX-formula, and it reduce performance. With Jamies solution you have already 3 filters in place.

Generally, if you have not much locations, you could choose for a slicer with a list of the locations.
If you want to select locations through numbering, you could add a Lookup table with the locations, adding a location-number column. ((Much) less favorable is adding a calculated column in your fact table with multiple conditions for numbering).

Hope it helps you a step further.
Kind regards JW

I think i am going to have to use the slicer as well, as there different dates and QOH for each number at each branch. or maybe do a measure for each branch

OK i had 2 versions installed the X64 version and another one fixed that