Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create an expression to show total sales for prior YTD. My Sales and Date are in the same table. I do not have a separate date table. Is there an expression that will work for this scenario?
Table Name is Trades
Fields are Gross Amount and Trade Date
Solved! Go to Solution.
Hi @CJANE
You can create a Measure like the following.
LYTD =
var year=YEAR(MAX(Trades[Trade Date]))-1
return CALCULATE(SUM(Trades[Gross Amount]), FILTER(ALL(Trades),Trades[Trade Date]>=DATE(year,1,1)&&Trades[Trade Date]<=MAX(Trades[Trade Date])))
The result looks like this:
For more details, you can refer the attached pbix.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CJANE
You can create a Measure like the following.
LYTD =
var year=YEAR(MAX(Trades[Trade Date]))-1
return CALCULATE(SUM(Trades[Gross Amount]), FILTER(ALL(Trades),Trades[Trade Date]>=DATE(year,1,1)&&Trades[Trade Date]<=MAX(Trades[Trade Date])))
The result looks like this:
For more details, you can refer the attached pbix.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You should not write any time intelligence function without a date table. The reason is that the time intelligence functions rely on a complete list of all days in the table to work. You can use the inbuilt date table if you like. Simply refer to it in locations where you must specify the date column, ie SAMEPERIODLASTYEAR(Trades[Date].[Date]). Note the extra .[Date]
Try any of the following 2 formulas:
LYTD = CALCULATE(SUM(Trades[Gross Amount]), SAMEPERIODLASTYEAR(Trades[Trade Date]))
LYTD = CALCULATE(SUM(Trades[Gross Amount]), DATEADD (Trades[Trade Date], -1, YEAR))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |