Filter by date column data

[Gab78]

Hi guys,

 

I have two tables, one date table with date columns, year, year+month, long date, week of the year, day of the week (e.g. Sunday=0, Monday =1, etc), and a last calculated column which is made up by concatenating the "year + week of the year+ day of the week".
The other table is sales, there I have the store code, the amount of the sale and the date.

I am trying that from a date selection, calculate the sales of the previous year but in relation to the same week and same day, show them in the same table.

I have created the following dax.

M.fct_salesLY =
VAR lastyear =
CALCULATE (
SUM (FCT_sales[sales]),
FILTER (ALL (date), date[YWDN] -1000 )
)
RETURN
IF(lastyear= BLANK(),0,lastyear)

Can you think of any other alternative.
Regards

[SamInogic]

Hi,

You can try it by creating a same column in sales Table by concatenating the "year + week of the year+ day of the week" 

After this, create a relationship between the Tables using column [YWON]

We have created below two tables:

Table 1:

Table 2:

Now, create a column using below DAX Expression

YWON = CONCATENATE([SD].[Year],CONCATENATE(WEEKNUM([SD].[Date],1),WEEKDAY([SD]))-1)

Next step will be to create a Relationship between both Tables,

Add a Slicer Visual and add Year column of Table 1, as shown in the below screenshot.

As we have created a Relationship between two tables using [YWON] column, we can filter the data by [YWON]
Green : [YWON] sales Table

Red : [YWON] First Table

When last year is selected in the Slicer then, it is also filtering the Sales Table by the [YWON] (Because of the Relationship).

For the Calculation, you can use the Column Aggregation functions available in Power BI as shown in the below screenshot.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Services

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523