Showing data while dataset is filtered

[pezakas]

Hello community,

This is my dataset. 

The user always picks a date from a single selection slicer, that is using the "Dataflow_Date".
I would like to create a measure so that when user select's the 31/1 of each year, he can see the SUM of effort in the previous year.

Thanks in advance!

[NaveenGandhi]

Hello @pezakas 

Your question is bit confusing. You want sum of effort of previous year when user selects 31/1. That would look like below in a table visual. What would you want to see when user selects 28/2?

31/1 selected:

Project ID	Effort(MDs)	Dataflow_Date
ID001	12	31/01/2023	(sum of effort of 2022)

[pezakas]

When user selects 28/02 , he should see the effort as of 31/01 of the same year.

[NaveenGandhi]

@pezakas 

Try the below measure.

Effort PRevious =
VAR Prev_=
IF (
MONTH ( MAX ( Effort[Dataflow_Date] ) ) = 1,
CALCULATE (
SUM ( Effort[Effort(MDs)] ),
ALL ( Effort ),
PREVIOUSYEAR ( Effort[Dataflow_Date] )
),
CALCULATE (
SUM ( Effort[Effort(MDs)] ),
PREVIOUSMONTH ( Effort[Dataflow_Date] )
)
)
RETURN
Prev_

Let me know if this helps.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[pezakas]

Does this work if you select the January from date slicer? Or it returns blank()?
When you set ALL(Effort) it means you clear the whole table from filters , right? 

[NaveenGandhi]

@pezakas 

It would not turn up blank if you select January, ALL(effort) here is used to basically neglect the project filter context, Since each year ID is different. 

I would suggest to remove the all(effort), Check the output, Then again remove project ID from the table to see whats happening.

Note: Allexcept would be better suited to understand this.

ALLEXCEPT(Effort,Effort[Dataflow_Date]) 

Let me know if this helps.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[pezakas]

Hm i see. Although, Project_ID has nothing to do with the year. It's the ID of the project, maybe i shouldn't mention it as a column in table at all. I will try what you suggested and share feedback again soon.