It makes sense to group by column1, but display column2 ?

[ovonel]

I have a Fact Revenue, with 2 dimensions: Customer and Category.

I have the below data:

Customer	Category	Revenue YTD	Revenue LYTD
x	OldCategory	(blank)	150
x	NewCategory	2000	(blank)

(In my pbix I have the below visual). There are just 1 or 2 customers that have changed category; so for that specific case, I see:

Category	Revenue YTD	Revenue LYTD
NewCategory	2000	(blank)

(This is correct, since the NewCategory has no revenue for the last year).

But, I don't want to see Revenue LYTD as blank; is there a way to display Category, but in fact do a group by Customer?... That way, I would be able to display:

Category	Revenue YTD	Revenue LYTD
NewCategory	2000	150

Code is:

Revenue YTD:=CALCULATE ( [Revenue] , DATESYTD('Date'[Date],"31/05") )

Revenue LYTD:=CALCULATE ( [Revenue YTD] , SAMEPERIODLASTYEAR('Date'[Date]) )

[v-easonf-msft]

Hi,  @ovonel 

Try the following formula:

Revenue LYTD =
VAR _lytd =
    CALCULATE ( [Revenue YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF ( ISBLANK ( _lytd ), [Revenue YTD], _LYTD )

If it doesn't meet your requirement, please share more details.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@ovonel , not very clear what output you want you can try values

CALCULATE ( [Revenue] , DATESYTD('Date'[Date],"31/05"), values (Table[Customer] ))

Revenue LYTD:=CALCULATE ( [Revenue YTD] , SAMEPERIODLASTYEAR('Date'[Date]), values (Table[Customer] ) )