Forum Discussion
Three year minimum in PowerBI
Hello!
I have transaction data that looks like this:
| Date | TransactionID | Value |
| 01/01/2019 | S52 | 5 |
| 01/02/2019 | F45 | 4 |
| 01/03/2019 | F62 | 8 |
| 01/01/2018 | C35 | 9 |
| 01/03/2018 | W98 | 1 |
| 01/02/2019 | S65 | 5 |
| 04/03/2019 | C45 | 1 |
| 01/01/2017 | S25 | 6 |
And I would like to create a visualization on a table (or chart ideally) that would look like this:
| 2019 number of transactions | 3 year minimum | |
| January | 1 | 1 |
| February | 2 | 0 |
| March | 2 | 0 |
It basically compares the number of transactions on a certain year with the minimum of the past five years.
Do you have ideas on how to do this?
Thanks,
5 Replies
- amitchandak
Super User
Create a date dimesnion and make it as date table
Rolling 5 year= CALCULATE(count(table[transaction ID]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-5,Year)) last year Sales = var _year = maxx(date,date[year])-1 CALCULATE(count(table[transaction ID]),all('Table'[Date]),year('Table'[Date])=_year) For this year or last year YTD Sales = CALCULATE(count(table[transaction ID]),DATESYTD(('Date'[Date]),"12/31")) Last YTD Sales = CALCULATE(count(table[transaction ID]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD Sales = CALCULATE(count(table[transaction ID]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))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/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 -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin - Mariusz
Community Champion
Hi Anonymous
Please see the attached file with both measures included, note that I've added Calendar Table to the model.
result
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.I already have a date table.
So if filter by one year, and I have as values in the Values field for the table:
Month
Total Count of Transactions
5 year min
The column 5 year min shows as 0, and it should say the minimum transaction number in the past five years including this one.
Only shows numbers if you have the five years on the table. If I only have 2019 for example on the table as column, the column 5 year minimum should still include the number of transactions minimum in the past 5 years.
- Mariusz
Hi Anonymous
I've adjusted the formula to ignore filter context coming from the Year slicer.
3 year minimum = VAR __count = CALCULATE( DISTINCTCOUNT( 'Calendar'[Year] ), CALCULATETABLE( 'Table', ALL( 'Calendar'[Year] ) ) ) RETURN IF( __count > 0, INT( __count >= 3 ), BLANK() )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
