Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need help with Measure Flexibility

The report I am building as a history of data for several quarters, specifically 2020Q1 to 2021Q2.  However, I wanted to create the most flexible tool possible, so that when new data is brought in, the tool automatically shows the most recent and prior quarters of data without having to select which quarters users want to view.  Right now, my current and prior calculation aren't working.  For example, I am calculating current and prior exposure, but for accounts in which there is data for the prior quarter, but not for the current quarter, the data is flowing through as current quarter data.

 

Example, Account ABC has data for 2021Q1 but then dropped out of the data.  However, it flows through the table like this:

Here are my measures for the data:

Current Qtr Exposure =
var currentqtr = MAX(Account_Data_Total[Quarter])
return
CALCULATE(SUMX(Account_Data_Total,Account_Data_Total[Exposure]),Account_Data_Total[Quarter]=currentqtr)
 
Prior Qtr Exposure =
var currentqtr = MAX(Account_Data_Total[Quarter])
var priorqtr = CALCULATE(MAX(Account_Data_Total[Quarter]),ALL(Account_Data_Total),Account_Data_Total[Quarter]<>currentqtr)
return
CALCULATE(SUMX(Account_Data_Total,Account_Data_Total[Exposure]),Account_Data_Total[Quarter]=priorqtr,ALL(Account_Data_Total[Office]),ALL(Account_Data_Total[Exposure Bucket]),ALL(Account_Data_Total[Producer]),ALL(Account_Data_Total[Producer (Shortened)]),ALL(Account_Data_Total[Contractor Category]),ALL(Account_Data_Total[Sector]),ALL(Account_Data_Total[Account]))
 
Please help me understand what additional filtering I need to add to resolve this problem.
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi 

     

    Can you upload the PBIX to OneDrive or Google drive and share the link.  Also for the data in the pbix , what is the result you expect ? Are you using slicers on date table ?

     

    Cheers

     

    CheenuSing

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    For security purposes I can't share the pbix, but here is a more complete look.  There is a slicer on the page, and what I would expect to see for Company ABC are blanks in the current columns, and those exact values currently populating but in the prior quarter columns.  I think the issue is that the max quarter is calculating at the account level, instead of overall.