Forum Discussion

PowerBi2bPro's avatar
PowerBi2bPro
Icon for Helper I rankHelper I
2 years ago

Date Function or DAX that returns value from a specific column

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...
talespin's avatar
talespin
Icon for Solution Sage rankSolution Sage
2 years ago

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) )
 

 

PowerBi2bPro's avatar
PowerBi2bPro
Icon for Helper I rankHelper I
2 years ago

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?

 

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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.