Forum Discussion
Filter by date column data
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
2 Replies
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-p/963216
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/1447523Hi,
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 TableRed : [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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
