Calculate difference of value change between 2 selected dates (Help)

[rlsmith]

I am trying to display a card visual that shows the % change in inventory value between 2 dates that I select in a between date slicer. I isolated the dax measure to see where it could be going wrong. 

First Issue: Only the starting date part of the measure works, so when I select 2 dates the starting date will show the inventory value for that date, but the ending date shows (Blank)

Second Issue: The starting value measure only works when I click on a column in my column chart, not when I use the slicer, so if I click February 2021 on the column chart it will show the correct value. If I only set the slicer say to February 2021 it will show the Inventory Value for the start of the dataset in January 2020. 
Details: There are only data points for the 1st day of each month, so January 1st 2020 has an inventory value, February 1st 2020, etc...

- The column charts including inventory value over time by Warehouse Type. 

Any help would be appreciated, Thanks!

Dax Measure: 

Inventory Value % Change = 
VAR SelectedStartDate = FIRSTDATE('Dax_Calendar'[Date])
VAR SelectedEndDate = LASTDATE('Dax_Calendar'[Date])

VAR SelectedStoreType = SELECTEDVALUE('Warehouse_Classification'[WH Type], BLANK())

VAR StartInventoryValue =
    CALCULATE(
        SUM('Inventory_History'[Value]),
        'Dax_Calendar'[Date] = SelectedStartDate,
        'Warehouse_Classification'[WH Type] = SelectedStoreType
    )

VAR EndInventoryValue =
    CALCULATE(
        SUM('Inventory_History'[Value]),
        'Dax_Calendar'[Date] = SelectedEndDate,
        'Warehouse_Classification'[WH Type] = SelectedStoreType
    )

VAR InventoryValueChange = EndInventoryValue - StartInventoryValue

VAR PercentageChange =
    IF(
        ISBLANK(StartInventoryValue) || ISBLANK(EndInventoryValue) || StartInventoryValue = 0,
        BLANK(),
        DIVIDE(InventoryValueChange, ABS(StartInventoryValue))
    )

RETURN
    PercentageChange

[v-yueyunzh-msft]

Hi , @rlsmith 

Accoridng to your description,  There are only data points for the 1st day of each month, dose it mean in your fact table there is just the  1st day of each month.

If this , you can try to modify the "SelectedStartDate" and "SelectedEndDate":

VAR SelectedStartDate = DATE(YEAR( MIN('Dax_Calendar'[Date]) ),MONTH( MIN('Dax_Calendar'[Date]) ) ,1)

VAR SelectedEndDate =DATE(YEAR( MAX('Dax_Calendar'[Date]) ),MONTH( MAX('Dax_Calendar'[Date]) ) ,1)

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rlsmith]

Ok, so I tried this 

VAR SelectedStartDate = DATE(YEAR( MIN('Dax_Calendar'[Date]) ),MONTH( MIN('Dax_Calendar'[Date]) ) ,1)

VAR SelectedEndDate =DATE(YEAR( MAX('Dax_Calendar'[Date]) ),MONTH( MAX('Dax_Calendar'[Date]) ) ,1)

It works, but the problem now is that it only shows the correct reduction percentage if the user selects the first of the month on the slicer. 

For example, if I select January 20th 2020 - August 1st 2023, it will show -100% reduction percentage which is not correct. If I select January 1st 2020 - August 1st 2023, it will show the correct reduction percentage. 

Another odd thing is that the SelectedEndDate works even if I selected say August 4th 2023, which I was hoping was true for the SelectedStartDate too.

[v-yueyunzh-msft · Accepted solution]

Hi ,  @rlsmith 

Thanks for your quick response and your sample data for me ! According to your data , you have three tables in your side . Dose the relationship like this:

I test it in my side it works good for this relationship using this dax code , we can create a measure:

Measure 2 = var _selectedStartDate = DATE(YEAR( MIN('Dax_Calendar Table'[Date]) ),MONTH( MIN('Dax_Calendar Table'[Date]) ) ,1)
var _selectedEndDate = DATE(YEAR( MAX('Dax_Calendar Table'[Date]) ),MONTH( MAX('Dax_Calendar Table'[Date]) ) ,1)
VAR StartInventoryValue = CALCULATE( SUM('Inventory_History Table'[Value]) , 'Dax_Calendar Table'[Date]=_selectedStartDate)
VAR EndInventoryValue = CALCULATE( SUM('Inventory_History Table'[Value]) , 'Dax_Calendar Table'[Date]=_selectedEndDate)
VAR InventoryValueChange = EndInventoryValue - StartInventoryValue
VAR PercentageChange =
IF(
ISBLANK(StartInventoryValue) || ISBLANK(EndInventoryValue) || StartInventoryValue = 0,
BLANK(),
DIVIDE(InventoryValueChange, ABS(StartInventoryValue))
)
return
PercentageChange

The result is as follows:

If the your "Dax_Calendar Table" have no relationship between your "Inventory_History Table", like this:

You can also try to use this dax code:

 

Measure 2 = var _selectedStartDate = DATE(YEAR( MIN('Dax_Calendar Table'[Date]) ),MONTH( MIN('Dax_Calendar Table'[Date]) ) ,1)
var _selectedEndDate = DATE(YEAR( MAX('Dax_Calendar Table'[Date]) ),MONTH( MAX('Dax_Calendar Table'[Date]) ) ,1)
VAR StartInventoryValue = CALCULATE( SUM('Inventory_History Table'[Value]) ,'Inventory_History Table'[Date]=_selectedStartDate)
VAR EndInventoryValue = CALCULATE( SUM('Inventory_History Table'[Value]) , 'Inventory_History Table'[Date]=_selectedEndDate)
VAR InventoryValueChange = EndInventoryValue - StartInventoryValue
VAR PercentageChange =
IF(
ISBLANK(StartInventoryValue) || ISBLANK(EndInventoryValue) || StartInventoryValue = 0,
BLANK(),
DIVIDE(InventoryValueChange, ABS(StartInventoryValue))
)
return
PercentageChange

You can download my pbix to check, if none of the above can meet your needs, you can check whether our relationship is correct, or I have misunderstood your needs? And you can provide me with some special data and your expected output according to the specific situation, thank you very much for your reply~

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rlsmith]

The calendar has all dates, because this page of the powerbi report is using a table with only the first day of each month, but other pages involved daily data.

Desired Output = I have a card visual. I want it display the reduction/increase in inventory as a percentage between 2 selected dates that the user selects using a between dates slicer. 

- Example, Value on 1/1/2020 in the sample data is $401,000. 
- Value on 8/1/2023 in the sample data is $200,500

- Percentage Change = -50%

The percentage change in value based on user selected dates in the slicer is what I want displayed in the card visual. The inventory value over time on the page is displayed in cluster column charts, so it would also be nice if it would show the percentage change based on the slicer dates and based on the Warehouse Type slicer selection.

Inventory_History Table - Inventory value of each part on the first of each month. 1/1/2020-Current Date

Value	Date	Part#	Warehouse
$190000	8/1/2023	abcd	PHOAZ-0000
$10500	8/1/2023	dacs	OLBMS-0000
$981	7/1/2023	dcba	FLAGNY-0000
$400000	1/1/2020	abdc	FLAGNY-0000
$1000	1/1/2020	dabc	FLAGNY-0000
$5010	7/1/2023	kajk	FLAGNY-0000

Dax_Calendar Table - Goes from 1/1/2020 to Current Date

Date	Year	Month
1/1/2020	2020	1
1/2/2020	2020	1
1/3/2020	2020	1
1/4/2020	2020	1
1/5/2020	2020	1
1/6/2020	2020	1
1/7/2020	2020	1
1/8/2020	2020	1
1/9/2020	2020	1
1/10/2020	2020	1
1/11/2020	2020	1
1/12/2020	2020	1
1/13/2020	2020	1
1/14/2020	2020	1
1/15/2020	2020	1
1/16/2020	2020	1
1/17/2020	2020	1
1/18/2020	2020	1
1/19/2020	2020	1
1/20/2020	2020	1
1/21/2020	2020	1
1/22/2020	2020	1
1/23/2020	2020	1
1/24/2020	2020	1
1/25/2020	2020	1
1/26/2020	2020	1
1/27/2020	2020	1
1/28/2020	2020	1
1/29/2020	2020	1
1/30/2020	2020	1
1/31/2020	2020	1

Warehouse_Type Table - shows all warehouses and their type and region.

Region	WH Type	Warehouse #
Southeast	Hub	FLAGNY-0000
Southwest	Field	PTSPA-0000
Northeast	Centralized	OLBMS-0000
Northwest	Decentralized	PHOAZ-0000

[rlsmith]

This sample data looks messed up, sorry. It was in table format when I posted it. Just act like there are spaces in there since some of the columns are running into eachother.