Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a formula that works great for calculating a running total of transactions between two dates in a date slicer:
Hi @apatnola ,
We can create a new measure based on your measure to meet your requirement.
Transactions Cumulative Current Period (last year) =
IF (
COUNTROWS ( 'Confirmed Transactions' ) > 0,
CALCULATE (
[Transactions Cumulative Current Period],
SAMEPERIODLASTYEAR (
FILTER ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] <= MAX ( Dates[Date] ) )
)
)
)
Then we can get the result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
Thank you, I tried:
Hi @apatnola ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @apatnola ,
We found that it miss a right brackets in the first measure and column name in the second measure, please try to use the following measure:
Transactions **bleep** Prior Period =
IF (
COUNTROWS ( 'Confirmed Transactions' ) > 0,
CALCULATE (
[Transactions **bleep** Current Period],
SAMEPERIODLASTYEAR (
FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
)
)
)
Best regards,
Something like this. With date calendar
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,year))))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Thank you, I tried:
Should be able to use PREVIOUSYEAR like:
Last year = CALCULATE([Transactions Cumulative Current Period],PREVIOUSYEAR('Dates'[Date]))
Also, if TI doesn't work See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
If I use previous year as follows:
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |