topic Date Function or DAX that returns value from a specific column in DAX Commands and Tips

Date Function or DAX that returns value from a specific column

PowerBi2bPro — Fri, 09 Feb 2024 18:01:41 GMT

Hi Everyone,

I am looking to get some direction. I am new to this forum and Power BI. I am sharing an example of the table to explain my question.

I have a dataset that has quarterly data for a few financial measures. Date and Total expense are part of it.

Year / Date colun contains date the way I have it displayed below. Correspondingly, we have value for Total Expense in the other column. The total expenses is a cumulative data for the year so Dec 2023 value contains total expenses of the year to date.

I want to calculate that difference between December and September 2023 to know what was the expense for the last quarter.

Can you please get me started? Thanks a lot in advance.

Year (Date)	Total Expense
December 31, 2023	10000
September 30, 2023	8000

Re: Date Function or DAX that returns value from a specific column

PowerBi2bPro — Fri, 09 Feb 2024 23:43:16 GMT

So my goal was to calculate the difference. I used Prv_Qtr_Assets = CALCULATE(sum ('Income Statement $'[Average Assets]), DATEADD('Income$MasterDate'[Date].[Date],-1,QUARTER)) this command to get side by side values for Assets but its not working as the column remains blank.

If there was value my hope was that I can just deduct one column from the other. Am I on right track?

Re: Date Function or DAX that returns value from a specific column

talespin — Mon, 12 Feb 2024 02:32:44 GMT

hi PowerBi2bPro ,

Is this what you need?

Logic Used

Find Expense at beginning of previous Quarter minus one day. That will give YTD till end of previous to previous quarter.

Then find expense at end of previous quarter

Subtract two.

Also I am using a Date table linked to Fact table one to many relationship on Date column.

Measure

-----------------

Expense Previous Qtr =

VAR _PreviousQtrMinus1Day = STARTOFQUARTER(PREVIOUSQUARTER(FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = TODAY()))) - 1

VAR _EndPreviousQtr = ENDOFQUARTER(PREVIOUSQUARTER( FILTER( ALL('CALENDAR'[Date]), 'CALENDAR'[Date] = TODAY()) ))

VAR _ValEndPreviousQtr = CALCULATE( VALUES(TestTbl6[YTD Expense]), REMOVEFILTERS(TestTbl6), 'CALENDAR'[Date] = _EndPreviousQtr)

VAR _ValBeginningPreviousQtr = CALCULATE( VALUES(TestTbl6[YTD Expense]), REMOVEFILTERS(TestTbl6), 'CALENDAR'[Date] = _PreviousQtrMinus1Day )

RETURN IF( ISBLANK(MAX(TestTbl6[Dt])) , BLANK(), IF( ISBLANK(_ValBeginningPreviousQtr), "N/A", _ValEndPreviousQtr - _ValBeginningPreviousQtr) )

Re: Date Function or DAX that returns value from a specific column

PowerBi2bPro — Wed, 14 Feb 2024 20:25:19 GMT

Thank you so much for sharing your thoughts. I have specific dates such as Dec 31 2023 that I am using as Quarter start/end date. I am having hard time explaining this. I will try again...

So date Columns has following entries

31 Dec 2023

30 Sep 2023

Expense Column has the following cooresponsing entries

15,000

10,000

Now lets say we wanted to know the difference between Sep and December expense. In this case it would be 5,000

How can I do this in Power BI?

Re: Date Function or DAX that returns value from a specific column

talespin — Thu, 15 Feb 2024 03:55:23 GMT

hi PowerBi2bPro ,

Create a Date table, this table should have all the dates(all dates from Min of your date column to maximum of date column), along with any other necessary columns like month, year.

Join this table to your table on Date column(Make sure data type is date).

Something like this

VAR _MinDate = MIN(Date column)

VAR _MaxDate = MAX(Date column)

CALENDAR(_MinDate, _MaxDate)

Then use the Measure.