topic Re: Calculate difference between monthly snapshots in historic table in DAX Commands and Tips

Calculate difference between monthly snapshots in historic table

Chris2016 — Tue, 21 May 2024 08:10:57 GMT

Hello,

I need some help with the following scenario.
I have a table where I am saving monthly captures of the sales status for Sales People in various departments.

Monthly capture	Department	Sales Person	Sales Category	# of Items sold	Latest Sales Date
Mar-24	X	A	Cars	2	1/5/2024
Mar-24	X	A	Planes	1	1/2/2024
Mar-24	X	A	Boats	3	1/13/2024
Mar-24	X	B	Cars	2	2/4/2024
Mar-24	X	B	Planes	1	2/5/2024
Mar-24	X	C	Cars	3	2/16/2024
Mar-24	X	C	Boats
Mar-24	Y	D	Boats	1	3/8/2024
Mar-24	Y	D	Cars	2	3/19/2024
Mar-24	Y	D	Planes	4	3/10/2024
Mar-24	Y	E	Boats	1	3/11/2024
Mar-24	Y	E	Cars	2	3/12/2024
Mar-24	Y	E	Planes		3/12/2024
Mar-24	X	F
Mar-24	X	G
Mar-24	Y	H
Mar-24	Y	I
Mar-24	Z	M	Planes	4	1/18/2024
Mar-24	Z	N	Boats	1	3/19/2024
Mar-24	Z	O	Cars	2	2/20/2024
Mar-24	Z	P	Planes
Apr-24	X	A	Cars	3	4/5/2024
Apr-24	X	A	Planes	2	1/2/2024
Apr-24	X	A	Boats	4	4/13/2024
Apr-24	X	B	Cars	3	4/4/2024
Apr-24	X	B	Planes	1	2/5/2024
Apr-24	X	C	Cars	3	2/16/2024
Apr-24	X	C	Boats
Apr-24	Y	D	Boats	1	3/8/2024
Apr-24	Y	D	Cars	3	4/19/2024
Apr-24	Y	D	Planes	4	3/10/2024
Apr-24	Y	E	Boats	3	4/11/2024
Apr-24	Y	E	Cars	2	3/12/2024
Apr-24	Y	E	Planes		3/12/2024
Apr-24	X	F	Cars	3	4/29/2024
Apr-24	X	G	Planes	1	4/30/2024
Apr-24	Y	H
Apr-24	Y	I
Apr-24	Z	M	Planes	4	1/18/2024
Apr-24	Z	N	Boats	1	3/19/2024
Apr-24	Z	O	Cars	3	4/30/2024
May-24	X	A	Cars	3	4/5/2024
May-24	X	A	Planes	2	1/2/2024
May-24	X	A	Boats	4	4/13/2024
May-24	X	B	Cars	3	4/4/2024
May-24	X	B	Planes	1	2/5/2024
May-24	X	C	Cars	3	2/16/2024
May-24	X	C	Boats
May-24	Y	D	Boats	3	5/18/2024
May-24	Y	D	Cars	3	4/19/2024
May-24	Y	D	Planes	4	3/10/2024
May-24	Y	E	Boats	3	4/11/2024
May-24	Y	E	Cars	3	5/12/2024
May-24	Y	E	Planes		3/12/2024
May-24	X	F	Cars	3	4/29/2024
May-24	X	G	Planes	3	5/20/2024
May-24	Y	H	Cars	3	5/20/2024
May-24	Y	I
May-24	Z	M	Planes	4	1/18/2024
May-24	Z	N	Boats	3	5/19/2024
May-24	Z	O	Cars	3	4/30/2024
May-24	Z	O	Boats	2	5/1/2024
May-24	Z	P	Planes	3	5/2/2024
May-24	Z	Q

The date (month and year) in column [Monthly capture] is a snapshot at the end of each calendar month. I add new data (current statuses) monthly to the existing data.

I am employing the following measures:

# Sales Person = DISTINCTCOUNT('Monthly Captures'[Sales Person])

# Sales People with over 3 items sold =
  COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
        FILTER ( 'Monthly Captures',  'Monthly Captures'[# of Items sold]>=3),
    "@result", 'Monthly Captures'[Sales Person])
     )
    )
// this is the number of sales people who have sold 3 or more items

% Sales over 3 =
IFERROR( DIVIDE([# Sales People with over 3 items sold], [# Sales Person], 0),0)
//this is the percent of sales people who have sold 3 or more items calculated against the entire sales population

I am also using the following measure, but it is not exactly what I need, as I need this measure to be unresponsive for the [Monthly capture] filter.

% Sales Current Month = 
CALCULATE (
    [% Sales over 3],
   FILTER('Monthly Captures', MAX('Monthly Captures'[Monthly capture])
))
// this measure should be the sales status of [% Sales over 3] for the current month and it should stay fixed,
and not respond to the [Monthly capture] filter.

The goal is to calculate how many people have sold 3 or more items in the current month snapshot of the data ( max( [Monthly capture]) ), and compare this status over any existing monthly snapshot.

This means that I need to create a system where I can have a table with Month and Year of [Latest Sales Date], the [% Sales over 3] for the Current Month (i.e. [% Sales Current Month] - this needs to be unresponsive to the [Monthly capture] filter), another measure (called [% Selected Monthly Capture] that responds to whatever month is selected in the [Monthly capture] filter, and a measure that is the difference ([% Difference]) between [% Sales Current Month] and [% Selected Monthly Capture] taking into consideration that, if data is missing in the [Latest Sales Date], it will not yield a result, but it will be 0 (meaning that, if, for instance, the [% Sales Current Month] = 86% for the month of May in [Latest Sales Date] and [% Selected Monthly Capture] is null for [Latest Sales Date] when selecting, for example, April in [Monthly capture], [% Difference] will not be 86% but 0, because there is nothing to compare between May and April.

Hope that makes sense and someone has an idea.

Many thanks!

Re: Calculate difference between monthly snapshots in historic table

Anonymous — Tue, 21 May 2024 12:51:49 GMT

Hi,Chris2016
I am glad to help you.

According to your description, you would like to calculate the percentage of salespeople with sales quantity greater than or equal to 3 recorded in different month snapshots. And you can compare any selected month's data with the most recent month's data.

Here are the results of my tests, hopefully they will help you .

# Sales Person = DISTINCTCOUNT('Monthly Captures'[Sales Person])

# Sales People with over 3 items sold = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Monthly Captures', 'Monthly Captures'[# of Items sold]>=3), "@result", 'Monthly Captures'[Sales Person]) ) )

% Sales over 3 = IFERROR( DIVIDE([# Sales People with over 3 items sold], [# Sales Person], 0),0)

I created the following measures based on my understanding.

% Sales Current Month unaffected by the [Monthly capture] slicer

% Sales Current Month = CALCULATE ( [% Sales over 3], FILTER( ALL('Monthly Captures'), 'Monthly Captures'[Monthly capture] = MAX('Monthly Captures'[Monthly capture]) ) )

% Selected Monthly Capture affected by the [Monthly capture] slicer

% Selected Monthly Capture = CALCULATE ( [% Sales over 3], FILTER( ALLSELECTED('Monthly Captures'[Monthly capture]), 'Monthly Captures'[Monthly capture] = MAX('Monthly Captures'[Monthly capture]) ) )

the final result “% Difference”:

% Difference = IF( ISBLANK([% Selected Monthly Capture]), 0, [% Selected Monthly Capture]-[% Sales Current Month] )

The above is the code I created based on your description, if my understanding is wrong, please let me know in time, and I will try my best to help you .

Here is the result

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate difference between monthly snapshots in historic table

Chris2016 — Wed, 22 May 2024 11:36:40 GMT

Many thanks for your thought on this, Carson.
the formula that contains the All function will not work, because it cancels all filters:

The way I've solved this is by having another table that has only the Current status, with all the current measures:

# Current Sales People = DISTINCTCOUNT('Current Status'[Sales Person])

# Current Sales People with over 3 items sold =
  COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
        FILTER ( 'Current Status',  'Current Status'[# of Items sold]>=3),
    "@result", 'Current Status'[Sales Person])
     )
    )

% Current Sales over 3 =
IFERROR( DIVIDE([# Current Sales People with over 3 items sold], [# Current Sales People], 0),0)

In the Monthly captures table, i added a measure to allow for the selection of any month to see the comarison with the current status:

% Selected Monthly Capture (Sales over 3) =
var result = CALCULATE (
    [% Sales over 3],
 FILTER('Monthly Captures', 
'Monthly Captures'[Monthly capture] = SELECTEDVALUE('Monthly Captures'[Monthly capture] )))
 return IF(ISFILTERED('Monthly Captures'[Monthly capture]),
    IF(HASONEVALUE('Monthly Captures'[Monthly capture]), result), BLANK())

Then, the difference between any month and Current:

-Difference = [% Current Sales over 3]-[% Selected Monthly Capture (Sales over 3)]

I am also using a Calendar table where Date has a relationship with Latest Sales Date in both Monthly Captures and Current Status table, and Latest Sales Month (in the snippit below) is the Month-Year format of Calendar Date.

There may be a better way where a Current Status table is not needed, but I did not find it yet 🙂

Many thanks for your assistence!