cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mmahachi Regular Visitor
Regular Visitor

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
Super User

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 220 members 2,447 guests
Please welcome our newest community members: