topic Re: Create a measure using another measure to filter by year.. in DAX Commands and Tips

Create a measure using another measure to filter by year..

sharpedogs — Sat, 22 Aug 2020 17:35:15 GMT

I have multiple columns that represent month over month invenstments. I want to sum up multiple columns and create a measures to put in a card so that it shows total investments for that year.

I created a measure "Total Contributions" which is simply a sum of all my contributions. Now I want to take my "Total contribution" measure and create a new measure so I can put it in a card to show three seperate data points

1) Total Contributions 2018

2) Total Contributions 2019

3) Total Contributions 2020

Dates	RRSP Contribution	TFSA Contribution	Equity contributions
jan 1 2018	10,000	3000	3000
Feb 4 2018	5000	4000	2000
Dec 1 2019	4000	5000	1000
Oct 3 2020	5000	7000	5000

Re: Create a measure using another measure to filter by year..

Anonymous — Sat, 22 Aug 2020 18:19:38 GMT

HI sharpedogs ,

Create a Calendar Table

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/

Create the relationship between dates of Calendar Table and Date of Contribution Table.

Now drag the year column from the Calendar Table into the visualization pane and the column RRSP Contribution, TFSA Contribution, Equity contributions from your Contribution Table.

Incase you want a Total sum of these three Columns

Total Contribution = SUMX('Table', 'Table'[Equity contributions] + 'Table'[RRSP Contribution] + 'Table'[TFSA Contribution])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

You need to drag

Re: Create a measure using another measure to filter by year..

sharpedogs — Sat, 22 Aug 2020 20:04:59 GMT

Hi Harsh,

That output is good for a column driven visual. I want to put the single value in card, I would prefer to do it using measure instead of filter.

A Measure where I would sum total investments then filter by year 2019... I can't seem to get the syntext correct.

Re: Create a measure using another measure to filter by year..

mahoneypat — Sat, 22 Aug 2020 21:49:44 GMT

Please try this expression to get your result (replace Table with your actual table name).

Total Contributions 2019 = CALCULATE([Total Contributions], FILTER(ALLSELECTED(Table[Dates]), YEAR(Table[Dates])=2019))

Or you could just put your [Total Contributions] measure in the card, and add a visual level filter in the Filter Panel on the Dates column.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Create a measure using another measure to filter by year..

Anonymous — Sat, 22 Aug 2020 22:10:03 GMT

Hi sharpedogs ,

Total Contribution 2018 = CALCULATE([Total Contribution], Filter(Dates, YEAR(Dates[Date]) = 2018))

Total Contribution 2019 = CALCULATE([Total Contribution], Filter(Dates, YEAR(Dates[Date]) = 2019))

Total Contribution 2020 = CALCULATE([Total Contribution], Filter(Dates, YEAR(Dates[Date]) = 2020))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Create a measure using another measure to filter by year..

amitchandak — Mon, 24 Aug 2020 04:40:18 GMT

sharpedogs , You can measure like this

Total = Sumx(Table,[RRSP Contribution]+ [TFSA Contribution]+ [Equity contributions]
or
Total = Sum(Table[RRSP Contribution])+ Sum(Table[TFSA Contribution])+ Sum(Table[Equity contributions])

The use of time intelligence

YTD = CALCULATE([Total]),DATESYTD('Date'[Date],"12/31")) Last YTD = CALCULATE([Total],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) This year = CALCULATE([Total],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last to last YTD = CALCULATE([Total],DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind = CALCULATE([Total],dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE([Total],filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE([Total],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) Last to Last Year = CALCULATE([Total],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-2))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

or like

2020 = CALCULATE([Total], filter(all(Table[Date]), Year(Table[Date]) = 2020))
2019 = CALCULATE([Total], filter(all(Table[Date]), Year(Table[Date]) = 2019))

Re: Create a measure using another measure to filter by year..

Anonymous — Mon, 24 Aug 2020 08:30:31 GMT

To program such things in accordance with the Power BI philosophy (which means "do things seamlessly, fast and reliable"), you should unpivot your table first. So, your table should have columns: Date, Contribution Type, Contribution Value. This can be done in Power Query easily. Contribution Type would contain 3 values: RRSP, TFSA and Equity. So, you need table T like this:

Date	Contribution Type	Contribution Value
2018-01-01	RRSP	10000
2018-01-01	TFSA	4000
2018-01-01	Equity	3000

Once you have it, the rest is dead easy. Your main measure would be:

Total = SUM( T[Contribution Value] )

Now you create a Dates table that covers all full years in terms of days and connect its [Date] field to the [Date] field in the above table. You hide [Contribution Value] and [Date] in T. You only leave [Contribution Type] exposed as you'll be slicing by it.

Now, if you put a slicer on the canvas that shows only the years from the Date table (which should be marked as the date table in the model) and start slicing by it, the above measure will adjust accordingly. If you make a slicer out of [Contribution Type], you'll also be able to see the individual contributions from individual types.

If you want to hard-code a value like [Total Contribution in Year XXXX] (which will not change according to selections), you can write:

[Total Contribution in Year XXXX] = var __year = XXXX return calculate( [Total Contribution], Dates[Year] = __year, ALL( T ) )

If, however, you want your measure to change according to all selections but the ones made in the Dates, you can write:

[Total Contribution in Year XXXX] = var __year = XXXX return calculate( [Total Contribution], Dates[Year] = __year, ALL( Dates ) )