Expiration Dashboard to track quantity and volume to expire in following days

[franorio]

Hello everybody, I'm trying to create an "expiration dashboard" in order to track and forecast upcoming expenditures.

I should measure quantity and volume/total amount ($$$) to expire in 4, 3, 2, and 1 week from the actual day I check the dashboard (it updates every single day bringing thousend of lines from MySQL).

Until now, all of my data is provided in one single table, direct from a MySQL server.

In order to forecast upcomings expirations from the actual date, my guess is I should create a Date/Time Table. But do not know which are the formulas to get my data in:

$xxxx will expire in a week from now, $xxxx in two weeks from now, $xxxx in three weeks from now.

Please if you could help me figuring this out, and which formulas i should use i would appreciate it very much. 

Also if you know any visualitation I can use similar to a calendar, that would show me in a very intuitive way the amount that expires every single day from today for the next 4 weeks it would be great!

Most important columns I need to work this out are the followings:

(Most of expirations date are at 30 days from emission date, others are "random")

ID	Invoice	Emission Date	Expiration Date	Amount
02-030-00206020-7	OMQ163	13/12/15	12/1/16	$ 2.151,
02-999-04540047-4	OMQ183	13/12/15	12/1/16	$ 2.151,
02-138-00228343-3	OMQ183	18/1/16	17/2/16	$ 2.248,5
02-061-00223703-1	OMQ183	24/1/16	23/2/16	$ 2.248,5
02-119-00040663-2	PMX200	2/3/16	1/4/16	$ 2.389,5
02-138-00237140-1	PMX200	24/4/16	24/5/16	$ 2.389,5
02-051-00276165-6	OMQ163	1/6/16	1/7/16	$ 2.695,5
02-105-00223829-5	PMX244	21/7/16	20/8/16	$ 2.964,
02-032-00189819-0	PMX244	23/7/16	29/8/16	$ 2.964,
02-046-00128680-5	PMX244	27/7/16	26/8/16	$ 2.964,
02-069-00135242-1	AA424ZB	18/8/16	17/9/16	$ 2.964,
02-069-00135243-1	AA424ZF	18/8/16	17/9/16	$ 2.964,
02-069-00135244-2	AA424ZG	18/8/16	27/9/16	$ 2.964,
02-069-00135241-0	AA424ZJ	18/8/16	17/9/16	$ 2.964,
02-102-00059873-8	PMX212	11/9/16	11/10/16	$ 2.964,
02-104-00194477-8	OMQ163	29/9/16	29/10/16	$ 2.964,
02-119-00126574-5	PLU454	6/10/16	25/11/16	$ 2.964,
02-032-00219169-6	OMQ183	7/10/16	6/11/16	$ 2.964,
02-045-00052378-5	OMQ183	7/10/16	26/11/16	$ 2.964,02
02-032-00216751-3	PEG526	12/10/16	11/11/16	$ 2.964,
02-119-00154573-5	PDV409	28/11/16	28/12/16	$ 988,02
10508396	PEG530	15/12/16	14/1/17	$ 12.240,
Q14517406	AA406QZ	29/12/16	28/1/17	$ 8.032,5
02-016-00201151-7	AA424ZA	1/1/17	31/1/17	$ 2.964,02
02-069-00205889-8	AA424ZA	1/1/17	31/1/17	$ 2.964,02
02-999-04613077-6	AA424ZA	11/1/17	19/2/17	$ 2.964,02
02-032-00251014-9	AA424ZA	11/1/17	10/2/17	$ 2.964,02
10529930	OKA567	14/1/17	13/2/17	$ 6.120,
Q14770320	PEG528	19/1/17	18/2/17	$ 2.677,5
02-138-00267137-5	PMX244	27/1/17	26/2/17	$ 2.964,02
02-030-00340041-8	PMX244	3/2/17	25/3/17	$ 2.964,02
B16608607	PEG528	16/2/17	18/3/17	$ 1.785,
C00780076	PDV382	7/3/17	6/4/17	$ 1.785,
B16849660	POW717	8/3/17	7/4/17	$ 1.785,
Q15325964	OMQ183	20/3/17	19/4/17	$ 1.785,
Q15169232	PEG555	2/4/17	2/5/17	$ 2.677,5
Q15428240	PEG528	18/4/17	18/5/17	$ 1.785,
10624399	AA087PL	20/4/17	20/5/17	$ 12.240,
B16921761	OMQ183	20/4/17	20/5/17	$ 1.785,
B16725476	AA406QZ	3/5/17	2/6/17	$ 1.785,
02-119-00215588-7	PMX244	24/5/17	23/6/17	$ 3.199,5
02-061-00371957-8	AB084QX	26/5/17	25/6/17	$ 3.199,5
02-119-00223351-0	PDV409	16/6/17	16/7/17	$ 3.199,5

Thanks a lot!

Best Regards

[v-jiascu-msft]

Hi @franorio,

Your sample data here are all expired. Is it just an example? 

Is the following your scenario? Is the Desired Result your solution?

ID     Expiration Date      Desired Result

A       12/15/2018           1 week

B        12/29/2018          2 week

Best Regards,
Dale

[franorio]

Hi @v-jiascu-msft, sorry for the delay.

What I'm trying to accomplish would be the following matrix.

	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total $ Week	Total Q Week
Week 1						$        4,302.00	$        4,497.00	$        8,799.00	4
Week 2	$ 2.389,5	$        8,049.00	$ 2.964,					$        8,049.00	5
Week 3	$        5,928.00			$        5,928.00			$        5,928.00	$      17,784.00	6
Week 4				$        5,928.00				$        5,928.00	2

Used the following info:

ID	Invoice	Emission Date	Expiration Date	Amount
02-030-00206020-7	OMQ163	13/12/15	29/12/2018	$ 2.151,
02-999-04540047-4	OMQ183	13/12/15	29/12/2018	$ 2.151,	4,302.00
02-138-00228343-3	OMQ183	18/1/16	30/12/18	$ 2.248,5
02-061-00223703-1	OMQ183	24/1/16	30/12/18	$ 2.248,5	4,497.00
02-119-00040663-2	PMX200	2/3/2016	31/12/2018	$ 2.389,5	$ 2.389,5
02-138-00237140-1	PMX200	24/4/16	1/1/2019	$ 2.389,5
02-051-00276165-6	OMQ163	1/6/2016	1/7/2019	$ 2.695,5
02-105-00223829-5	PMX244	21/7/16	1/1/2019	$ 2.964,	8,049.00
02-032-00189819-0	PMX244	23/7/16	2/1/2019	$ 2.964,	$ 2.964,
02-046-00128680-5	PMX244	27/7/16	7/1/2019	$ 2.964,
02-069-00135242-1	AA424ZB	18/8/16	7/1/2019	$ 2.964,	5,928.00
02-069-00135243-1	AA424ZF	18/8/16	10/1/2019	$ 2.964,
02-069-00135244-2	AA424ZG	18/8/16	10/1/2019	$ 2.964,	5,928.00
02-069-00135241-0	AA424ZJ	18/8/16	13/1/19	$ 2.964,
02-102-00059873-8	PMX212	11/9/2016	13/1/2019	$ 2.964,	5,928.00
02-104-00194477-8	OMQ163	29/9/16	17/1/19	$ 2.964,
02-119-00126574-5	PLU454	6/10/2016	17/1/19	$ 2.964,	5,928.00

Standing today, just want to see total to pay by day and by week. only for the upcoming 4 weeks

Thanks!

[Ashish_Mathur]

Hi,

How does one get the week numbers?

[v-jiascu-msft · Accepted solution]

Hi @franorio,

Please download the demo from the attachment. Since it's dynamic, the snapshot below is a little different from yours.

1. Create a Date table.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "DayName", FORMAT ( [Date], "dddd" ),
    "WeekDay", WEEKDAY ( [Date], 2 ),
    "Week", SWITCH (
        TRUE (),
        [Date] >= TODAY ()
            && [Date]
                <= TODAY () + 6, "Week 1",
        [Date]
            >= TODAY () + 7
            && [Date]
                <= TODAY () + 13, "Week 2",
        [Date]
            >= TODAY () + 14
            && [Date]
                <= TODAY () + 20, "Week 3",
        [Date]
            >= TODAY () + 21
            && [Date]
                <= TODAY () + 27, "Week 4"
    )
)

2. Establish a relationship.

3. Just drag these columns to the Matrix visual.

Best Regards,
Dale