cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mmahachi Helper I
Helper I

selected value is not taking alternate

hoping this is pretty simple... I have a dax measure that is supposed to supply the last fiscal period if nothing is selected, the problem is it just shows blank unless the fiscal period is in the visual either as an axis or a filter. I just want the card to show total revenue.

 

Total_Revenue = var FYPeriod = SELECTEDVALUE('Acct Period'[FiscalYearPeriod], LASTNONBLANK('Acct Period'[FiscalYearPeriod], SUM('Proj Detail'[YTDIncurredAmount])))
Return
CALCULATE(SUM('Proj Detail'[YTDIncurredAmount]), 'Proj Detail'[RateType] <> "Actuals", 'CNPAccount'[SummaryCategory] = "Revenue" || 'CNPAccount'[SummaryCategory] ="Revenue Adjustment",'Acct Period'[FiscalYearPeriod] = FYPeriod)

1 REPLY 1
Super User IV
Super User IV

Re: selected value is not taking alternate

-- You should create a measure first (name it something sensible, not Measure):

[Measure] = SUM ( 'Proj Detail'[YTDIncurredAmount] ) 

-- Then try this (measures and columns exposed in reports should not have underscores in their names):

[Total Revenue] =
VAR FYPeriod =
if(
    HASONEVALUE( 'Acct Period'[FiscalYearPeriod] ),
    VALUES ( 'Acct Period'[FiscalYearPeriod] ),
    LASTNONBLANK (
    	'Acct Period'[FiscalYearPeriod],
        [Measure]
   )
)
RETURN
    CALCULATE (
        [Measure],
        'Proj Detail'[RateType] <> "Actuals",
        TREATAS(
        	{ "Revenue", "Revenue Adjustment" },
        	'CNPAccount'[SummaryCategory]
        ),
        'Acct Period'[FiscalYearPeriod] = FYPeriod
    )

The above might or might not work. This depends on the logic you want but since I can't know what you want, I'm giving you what I guess could work in your case. Bear in mind that boolean filters in CALCULATE overwrite filters coming from outside.

 

Best

Darek



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.