Previous Edition - No Date dimension

Hi Everyone,

I have scenario where i cant use time dimension for time series calculations based on calendar but still need previous edition sales. below is sample data

Book2.xlsx (9.5 KB)

Please can you help with some inputs.

Thanks
Swapnil

Hi All,

I could get the results with below formula as expected but not able to see correct grand total in the table.
please can any one suggest me on how to correct

PrevEditionRevenue Test =
VAR PrevEE =
    SELECTEDVALUE ( 'Event Edition'[Previous_edition_num] )
VAR Evt =
    SELECTEDVALUE ( 'Event Edition'[Event_Name] )
RETURN
    CALCULATE (
        [Total Revenue],
        FILTER ( ALL ( 'Event Edition' ), 'Event Edition'[Event_Name] = Evt ),
        FILTER ( ALL ( 'Event Edition' ), 'Event Edition'[Event_edition_num] = PrevEE )
    )

@swapnil

What would you want to see in the total line?

Enterprise%20DNA%20Expert%20-%20Small

Hi All,

Please find the sample pbix, where you can see the total row for Previous edition revnue is not getting calculated when all events selected. Appreciate if you can suggest a solution.

SampleForTotal.pbix (47.3 KB)

Thanks
Swapnil

@swapnil
What value would you expect to see there? What would be “previous” at the grand total level? There are a few ways to handle that. Could be the value of the last day, average of all the previous revenue, sum, etc

Enterprise%20DNA%20Expert%20-%20Small

@Nick_M,
I am expecting sum of all rows under previous editions revenue displayed in the visual

@swapnil
Can you see if this is what you are looking for:

Pre Rev with Total =
SUMX(
VALUES( ‘Event Edition’[Event_Name]),[PrevEditionRevenue Test] )

image

Enterprise%20DNA%20Expert%20-%20Small

1 Like

@Nick_M, Thanks for this. this works only when i have a slicer on Event edition number but.
There is a case where user dont have have any slicers but want to see previous edition revenue and the total. and thats where it fails again.
Appreciate your inputs on this.

Thanks-Swapnil

image

Assuming your filters are coming from the Events Edition table:

Prev Rev Total = 
sumx( 
    ALLSELECTED('Event Edition'),
    [PrevEditionRevenue Test]
)

Enterprise%20DNA%20Expert%20-%20Small

1 Like

@Nick_M Thanks for your quick response.
User want to use the data model for ad-hoc querying and assuming the user dont have any knowledge of the data model and slicers to select. in those scenarios also the user should see data correctly. even without event or event edition slicers.

The solution suggested works if all events selected… but i dont want to have any slicers then it wont work.

Thanks
Swapnil

@swapnil

I see. what about this. There are no slicers activated, but there are filters (Event Edition Num and Event Name). I was trying not to iterate the events edition table, but I cant think of a different way where it would still be dynamic enough

Total Prev Rev Total = 
SUMX(
    'Event Edition',
    [PrevEditionRevenue Test]
)

Enterprise%20DNA%20Expert%20-%20Small

@Nick_M, No, they wont accept… they do not know about filters :slight_smile:

I found one solution here… much simpler but had to made changes to data model.
Since my original development is on a data base, i kind of tweeked the query while importing the fact table.
in the fact table populated a new column previous event edition wid by joining with event edition table. then it made life simple. now i can join event table row wid with event edition wid and keep it as active join. then join row wid with previous event edition wid make it as inactive join.
used userelationship function and it worked.

Thanks for your help and support. much appreciated.

Best regards
Swapnil

Ok, well if you found your solution I guess that’s all that matters. But I dont have idea what you mean here.

Filters are pretty much why DAX works the way it does and why it’s so powerful. But that’s neither here-nor-there. Glad you got your solution.

Enterprise%20DNA%20Expert%20-%20Small

@Nick_M, Sure i understand your point. its a challenge for us to educate users now. I will update with the solution

@swapnil,
No explanation needed. I was just a little murky on what exactly you were after, but hey, we got the end result needed!

Enterprise%20DNA%20Expert%20-%20Small

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.