How to write DAX to filter only specific rows dynamically even when data is changed in the future.

[Deeintu]

Hello Guys,

I'm trying to filter data to show the result on specific criteria from the below data dynamically even when the data gets updated but the logic should remain same..

Expected Result:

I need to use line chart to show "Estimated" values from November 2021 to June 2022.

Please let me know.

Thanks in advance.

Dee

[Jihwan_Kim]

Hi, 

I tried to follow the concept and created a sample pbix file like attached.

Please check the below picture and the attached pbix file.

Estimated measure: =
VAR newtable =
FILTER (
SUMMARIZE ( ALL ( Data ), 'Calendar'[Date], Data[Type] ),
AND ( Data[Type] <> "Actual", Data[Type] <> "Current" )
)
VAR groupbymindate =
GROUPBY (
newtable,
Data[Type],
"@mindate", MINX ( CURRENTGROUP (), 'Calendar'[Date] )
)
VAR filter_table =
FILTER (
CALCULATETABLE ( Data, TREATAS ( groupbymindate, Data[Type], Data[Date] ) ),
Data[Type] = MAX ( Data[Type] )
&& Data[Date] IN VALUES ( 'Calendar'[Date] )
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month & Year] ),
IF (
SELECTEDVALUE ( Data[Type] ) = "Current",
SUM ( Data[Estimated] ),
SUMX ( filter_table, Data[Estimated] )
)
)

[Deeintu]

Thank you very much @Jihwan_Kim  that's a great solution.

But the line chart is broken with the points. Please see below

Thanks

Dee

[Jihwan_Kim]

Hi,

Thank you for your feedback.

May I know how your expected result looks like?

[Deeintu]

Hi @Jihwan_Kim ,

My expected result should be like below.

Thanks in advance

Dee

[Jihwan_Kim]

Hi,

Please try to remove the legend in the linechart.

Thank you.

[Deeintu]

Hi @Jihwan_Kim 

When I remove the legend that future dates are disappered.

Please see below.

Can we wrap up all the "Type" field values into one single column? is it possible?

Thanks in advance

Dee

[Jihwan_Kim · Accepted solution]

Hi,

Sorry for my misunderstanding.

Please try the below.

Estimated measure: =
VAR newtable =
FILTER (
SUMMARIZE ( ALL ( Data ), 'Calendar'[Date], Data[Type] ),
AND ( Data[Type] <> "Actual", Data[Type] <> "Current" )
)
VAR groupbymindate =
GROUPBY (
newtable,
Data[Type],
"@mindate", MINX ( CURRENTGROUP (), 'Calendar'[Date] )
)
VAR filter_table =
FILTER (
CALCULATETABLE ( Data, TREATAS ( groupbymindate, Data[Type], Data[Date] ) ),
Data[Type] = MAX ( Data[Type] )
&& Data[Date] IN VALUES ( 'Calendar'[Date] )
)
VAR monthyeartable =
SUMMARIZE (
ADDCOLUMNS (
SUMMARIZE ( Data, 'Calendar'[Month & Year], Data[Type] ),
"@result",
CALCULATE (
IF (
SELECTEDVALUE ( Data[Type] ) = "Current",
SUM ( Data[Estimated] ),
SUMX ( filter_table, Data[Estimated] )
)
)
),
'Calendar'[Month & Year],
[@result]
)
RETURN
IF (
HASONEVALUE ( 'Calendar'[Month & Year] ),
SUMX ( monthyeartable, [@result] )
)

[TheoC]

@Jihwan_Kim brilliant! 👏🙌

[Deeintu]

Thanks  @Jihwan_Kim for your time and effort, great solution.

Regards 

Dee

[TheoC]

@Deeintu I think you need to remove the Legend in your line chart. I think @Jihwan_Kim has provided the right solution from what I see.

[Deeintu]

Hi @TheoC ,

When I remove the Legend then It  automatically removes other future months estimates from the chart.

Please see below.

Thanks

Dee

[amitchandak]

@Deeintu , Create a measure like

Calculate(Sum(Table[Estimate]), filter(Table, Table[Date] = minx(filter(allselected(Table), Table[Type] = max(Table[Type])),[Date]) && Table[Type] in {"02-Update", "05-TNP", "06-APP")) + Calculate(Sum(Table[Estimate]), filter(Table, Table[Type] = "01-Current"))

[Deeintu]

Hi @amitchandak 

Thank you very much for the response.

I'm getting below error in the DAX calculation.

Thanks

Dee