Three year minimum in PowerBI

[Anonymous]

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,

[Mariusz]

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.

[Anonymous]

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.

[Anonymous]

I tried that on my model and it only shows 1s all the time...

And it does not let me filter by years in case I would like to do it months in 2018 for example.

The logic behind it seems very simple, but the DAX formulas do not seem to have an easy solution!

[amitchandak]

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