Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Trying to get sales and total for 2020 to date (i.e 21/09/2021 current date) in this matrix
I'm stuck till here 😏
PY Intake =
CALCULATE([Total],
FILTER('Date', 'Date'[YearAct]=YEAR(TODAY())-1))
Solved! Go to Solution.
You need to have a "Day_Num" column in your date table which is simply DAY('Date'[Date])
TotalSalesCY =
CALCULATE ( [TotalSales], FILTER ( 'Date', 'Date'[Year] = YEAR ( TODAY () ) ) )
TotalSalesLY =
CALCULATE (
[TotalSales],
FILTER (
'Date',
'Date'[Year]
= YEAR ( TODAY () ) - 1
&& 'Date'[Month_Num] <= MONTH ( TODAY () )
&& 'Date'[Day_Num] <= DAY ( TODAY() )
)
)
Hi @brinky ,
See if this helps.
1) The Year filter on the page is from the Date Table
2) The bottom left table is just to show all the data that exists
3) The bottom right table is the summarized view which shows the data for the same period last year
There must be a relationship between the data table and the date table
Here are the two measures
TotalSales = SUM('Table'[Sale])
TotalSalesLY = CALCULATE([TotalSales],SAMEPERIODLASTYEAR('Date'[Date]))
Thanks for your reply.
I'm tring to obtain last year sale to date without any filters or slicers, this is my code for this year's sales data.
CALCULATE([Total],
FILTER('Date','Date'[YearAct]=YEAR(NOW())))
Now I would like to have for same for last year.
Thank you
There are plenty of way to do this (some possibly better) but this should get you the result.
Thanks,
Moiz
If this post helps, please "Accept" it as Solution to help other members find it.
TotalSalesCY =
CALCULATE ( [TotalSales], FILTER ( 'Date', 'Date'[Year] = YEAR ( TODAY () ) ) )
TotalSalesLY =
CALCULATE (
[TotalSales],
FILTER (
'Date',
'Date'[Year]
= YEAR ( TODAY () ) - 1
&& 'Date'[Month_Num] <= MONTH ( TODAY () )
)
)
Thanks Moiz,
Neraly there 😄 total should be 2,155,503 as per table below
You need to have a "Day_Num" column in your date table which is simply DAY('Date'[Date])
TotalSalesCY =
CALCULATE ( [TotalSales], FILTER ( 'Date', 'Date'[Year] = YEAR ( TODAY () ) ) )
TotalSalesLY =
CALCULATE (
[TotalSales],
FILTER (
'Date',
'Date'[Year]
= YEAR ( TODAY () ) - 1
&& 'Date'[Month_Num] <= MONTH ( TODAY () )
&& 'Date'[Day_Num] <= DAY ( TODAY() )
)
)
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |