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.
Hi,
I'm not sure why the card always defaulting to a combined year result rather than just the current year.
Here are the measures I use:
Solved! Go to Solution.
In case you have joined on dates you can use
Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))
Make sure tables are joined on dates and the calendar table is marked as date.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Or try
This year using year = CALCULATE(sum(Sales[Sales Amount]),Year(Sales[Sales Date]) =Year(today()))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @Xandman ,
We can create a measure to meet your requirement:
Total Revenue Of Last Year =
VAR LastYear =
CALCULATE ( YEAR ( MAX ( 'dDate'[Date] ) ), ALLSELECTED ( 'dDate' ) )
RETURN
CALCULATE (
[Total Accounts],
dCategoryOrder[CATEGORY] = "REVENUE",
FILTER ( ALLSELECTED ( 'dDate' ), YEAR ( 'dDate'[Date] ) = LastYear )
)
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,
Thanks for the replies!
The year isn't limited to 2018 and 2019, I've just been working on a sample data. If you look at the images attached, dDate has a relationship fFinStatResults[Month] -> this contains 1/31/2018 for example formatted to "Jan 2018", dDate[DATE] is 1/31/2018 12:00 AM
The relationship is confirmed when you select a year on the slicer, my problem is if the slicer is blank, it combines all the years of the data.
Hi @Xandman ,
We can create a measure to meet your requirement:
Total Revenue Of Last Year =
VAR LastYear =
CALCULATE ( YEAR ( MAX ( 'dDate'[Date] ) ), ALLSELECTED ( 'dDate' ) )
RETURN
CALCULATE (
[Total Accounts],
dCategoryOrder[CATEGORY] = "REVENUE",
FILTER ( ALLSELECTED ( 'dDate' ), YEAR ( 'dDate'[Date] ) = LastYear )
)
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,
Thank you for the help!
I now understand solution everyone was giving, I could not just create a universal measure that includes the dates without specifying the range.
I marked both as solutions, hope that counts.
Thanks again!
In case you have joined on dates you can use
Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))
Make sure tables are joined on dates and the calendar table is marked as date.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Or try
This year using year = CALCULATE(sum(Sales[Sales Amount]),Year(Sales[Sales Date]) =Year(today()))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @Xandman
To me, it seems the relationship is missing b/w newly created "dDate" Calendar Table and fFinStatResults. Please check after creating a relationship. If issue still occurs, then share PBIX file or sample data.
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |