Table with two dates field

[JuanviWebbeds]

Hi,

I have a fact table with two dates field, creation and action. (many rows here only and example)

Creation	ActionDate	NumberOfActions
01/02/2020	26/02/2020	26
01/02/2020	27/02/2020	21
02/02/2020	03/02/2020	13

This is sample data, we have N Action Date by 1 Creation Date summarize in the Numbe Of Actions

We want to build a char, X Axis withe creation Date, Y axis number of actions (this year and last year), filtered by a Action Date Slicer

In the visual we want to make a comparation with Last Year number of actions.

Using the sameperiodlastyear works for last year, but using the filter of Action Date Filter obviously filters the data to this days , and it only shows actions of last year to the action date in the filter, how do we apply the same comparation of Last Year Creations and same Action Date Last Year?

Thanks in advance.

[Anonymous · Accepted solution]

Hi there.

I think I've solved your problem. In the file with the solution, please note that there is bi-filtering enabled between 2 tables, Actions and Action Dates. This is not necessary and should be removed in a full solution but I've put it in there to filter the Action Dates in a slicer for easier navigation. But in a production model, it should be removed and not relied upon unless you know what you are doing, why you are doing it and what the consequences of this are. Otherwise, please stay away from bi-directional filtering as it can be dangerous.

When scrutinizing the solution, please pay attention to the data in the tables. Both date tables are marked as a Date table in the solution to be able to use time-intel functions against them.

Here's an outline of how it works. If you don't select any date from Action Date, then you compare [Total Actions] against [Total Actions PY] in the usual way. When you filter by dates in Action Date, then not only [Creation Dates] are shifted back one year for comparison but also Action Dates. This I believe is how you wanted it.

https://1drv.ms/u/s!ApyQEauTSLtOgYM98Uqy2CW0t8Whmg?e=3tjIAg

Best

D

[vivran22]

Hello @JuanviWebbeds ,

You can make use of a calendar table with dual relationships(one active and another inactive):

Then you can use measures USERELATIONSHIP with CALCULATE:

Orders by Order Date = SUM(dtOrders[Order Quantity])

Orders by Ship Date =
CALCULATE (
    dtOrders[Orders by Order Date],
    USERELATIONSHIP ( dtOrders[Ship Date], dtCalendar[Date] )
)

Orders (SD - PY) =
CALCULATE (
    dtOrders[Orders by Ship Date],
    SAMEPERIODLASTYEAR ( dtCalendar[Date] )
)

Then you can use these measures in your visuals:

Sample solution file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

Hi @vivran22.

One calendar in the solution will not achieve what's required. You have to have 2 of them out of necessity.

Best
D