Forum Discussion
Display value with Month
I have a set of data with Actual Values in one column and Target Values in second column, for each month.
I cannot figure out how to display those 2 values for each corresponding month side by side showing zero value as well.
Logic I am trying to have is;
Value1 and Value2 for each month should be in one row but past month value from value1 (table1), current month value is greater between value1 and value2 and future month value from value2(table2)
pbix here
Thank you for your help
Hi topazz11 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _today=TODAY() var _value1=MAX('Table1'[Value1]) var _value2=CALCULATE(SUM('Table2'[Value2]),FILTER(ALL(Table2),'Table2'[ID]=MAX('Table1'[ID])&&'Table2'[Date]=MAX('Table1'[Date]))) return SWITCH( TRUE(), MONTH(MAX('Table1'[Date]))=MONTH(_today), IF( _value1>_value2,_value1,_value2) , MONTH(MAX('Table1'[Date]))<MONTH(_today),_value1, MONTH(MAX('Table1'[Date]))>MONTH(_today), _value2 )2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
topazz11 , You need to have a common ID and Date column and then try a measure like
max(Sum(table[Value]), Sum(Table2[value]))+0
or
Sumx(Values(ID[ID]) calculate(Sumx( values(Date[Date]), calculate(max(Sum(table[Value]), Sum(Table2[value]))+0 ) )))
Hi topazz11 ,
Here are the steps you can follow：
1. Create measure.
Measure = var _today=TODAY() var _value1=MAX('Table1'[Value1]) var _value2=CALCULATE(SUM('Table2'[Value2]),FILTER(ALL(Table2),'Table2'[ID]=MAX('Table1'[ID])&&'Table2'[Date]=MAX('Table1'[Date]))) return SWITCH( TRUE(), MONTH(MAX('Table1'[Date]))=MONTH(_today), IF( _value1>_value2,_value1,_value2) , MONTH(MAX('Table1'[Date]))<MONTH(_today),_value1, MONTH(MAX('Table1'[Date]))>MONTH(_today), _value2 )2. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
