cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bd_8_bc
New Member

Power BI Functions

Hi 

Relatively new to using Power BI and just a bit stuck on something.

 

I have my data table and the table I'am building in Power BI as below:

 

Data Table

Statement Month    Cust ID.      Charge Type         Charge rate %

May 22                      AB1             1                           5%

May 22                       AB1             2                           10%

June 22                      AB1            1                             7%

June 22                      AB1             2                            8%

 

Power BI Table (i'm creating)

Cust ID.         Charge Type       Charge Rate

AB1                 1                        7%

AB1                  2                       8%

 

The rates for each customer change on a monthly basis within the raw data, I need to pull through to my table the latest set of rates for that customer for the correct charge type based on the latest/ MAX statement date, how can I achieve this?      

 

Many thanks

1 ACCEPTED SOLUTION

@bd_8_bc,

 

I entered your sample data and got the desired outcome. If you can share your pbix via one of the file services like OneDrive, I'll take a look.

 

DataInsights_0-1660051566422.png

 





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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
bd_8_bc
New Member

Hi and thanks for taking the time to respond.

 

It does work but I may not have been entirely clear with my question. It looks to the latest statement date however I did not mention that the customer ID may not appear in all months so it may not always be the latest months statement if the customer did not appear and could be May's statement date where the latest rate appears for the customer.

 

The measure does pick up the correct rates but only for customers who appear within the latest statement date but not all customers appear every month, so the measure would need to identify the latest statement month the cust ID appears in and then return the rate from there? Thanks  

Hi, @bd_8_bc 

Can you retry DataInsights's approach?

On my side, the measure also works fine for different customers.

If your problem persists, please provide a sample file for further research.

 

Best Regards,
Community Support Team _ Eason

 

@bd_8_bc,

 

Would you be able to provide an example of this scenario and screenshots of current and expected results? In my testing, I'm not seeing the issue.





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

Proud to be a Super User!




Hi please see screenshot below of sample of data and expected outcomes, thanks

bd_8_bc_1-1660047164625.png

 

@bd_8_bc,

 

I entered your sample data and got the desired outcome. If you can share your pbix via one of the file services like OneDrive, I'll take a look.

 

DataInsights_0-1660051566422.png

 





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

Proud to be a Super User!




Hi I have re entered the formula and it is now working so the error was on myside, apologies for this and thanks for your help and time spent on this, it now works perfectly.

DataInsights
Super User
Super User

@bd_8_bc,

 

Try this measure. In this example, I made Statement Month a date (used the 1st day of the month).

 

Latest Charge Rate = 
VAR vMaxDate =
    CALCULATE (
        MAX ( 'DataTable'[Statement Month] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Cust ID], 'DataTable'[Charge Type] )
    )
VAR vResult =
    CALCULATE (
        MAX ( 'DataTable'[Charge Rate %] ),
        'DataTable'[Statement Month] = vMaxDate
    )
RETURN
    vResult

 

DataInsights_0-1658415600757.png

 





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

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors