Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am new to PowerBI, and would greatly appreciate some help. I am attempting to populate a matrix with values of three accounts (Savings, Checking, and Certificate) I am having trouble when one of the accounts has a value for a date, but another account does not. I am looking for there to be no blanks in my table. The most recent value should be assumed for any date that has no data for that day (ie Checking June 14 2018 should equle 1535.94). I have tried using the LastNonBlank to no avail.
Solved! Go to Solution.
Hi @Anonymous ,
For starting don't use the bidirectionallity of filter then create the following measures:
Savings sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings) ); CALCULATE ( MAX ( Savings[Savings]); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) Certicate sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate) ); CALCULATE ( MAX ( Certificate[Certificate] ); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) Checking sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) ); CALCULATE ( MAX ( Checking[Checking] ); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
Final result is below:
Check also PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
You are correct, because I used the MAX function on the values, need to pickup the maximum date and not maximum value.
Redo your measure to the following:
Savings sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Savings[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings ) ); CALCULATE ( SUM ( Savings[Savings] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
Certicate sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Certificate[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate ) ); CALCULATE ( SUM ( Certificate[Certificate] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
Checking sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Checking[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) ); CALCULATE ( SUM ( Checking[Checking]); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
See result below:
Also attached revised PBIX file.
Sorry for the misleading in the previous answer.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
For starting don't use the bidirectionallity of filter then create the following measures:
Savings sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings) ); CALCULATE ( MAX ( Savings[Savings]); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) Certicate sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate) ); CALCULATE ( MAX ( Certificate[Certificate] ); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) Checking sum = IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) ); CALCULATE ( MAX ( Checking[Checking] ); FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
Final result is below:
Check also PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you, This is working, but only when the values in the accounts are going up. If there is a drop in one of the accounts, it retains the largerst value.
Hi @Anonymous ,
You are correct, because I used the MAX function on the values, need to pickup the maximum date and not maximum value.
Redo your measure to the following:
Savings sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Savings[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings ) ); CALCULATE ( SUM ( Savings[Savings] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
Certicate sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Certificate[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate ) ); CALCULATE ( SUM ( Certificate[Certificate] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
Checking sum = VAR Date_Selection = CALCULATE ( LASTDATE ( Checking[Date] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) RETURN IF ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) ); CALCULATE ( SUM ( Checking[Checking]); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection ) ) )
See result below:
Also attached revised PBIX file.
Sorry for the misleading in the previous answer.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Share the link from where i can download your PBI file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |