Forum Discussion

sarath_chandra's avatar
sarath_chandra
Icon for Helper III rankHelper III
3 years ago
Solved

Dax help

Hi all, i have week date filter , if i select one date (current week date) and previous week date is prior week date that should be populated in table visual and i need difference of both dynamically...
DAX
Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

     

     

    Usage prior week: =
CALCULATE (
    [Usage current week:],
    OFFSET ( -1, ALL ( 'Calendar'[Year-Wk sort], 'Calendar'[Year-Wk] ) )
)

     

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
3 years ago

sarath_chandra Hi! Here is the measure to calculate dinamically the Difference:

 

Difference =
VAR SELECTEDWEEK = SELECTEDVALUE(DISTINCTTABLE[Date])
VAR PREVWEEK = CALCULATE(MAX(DISTINCTTABLE[Date]), ALL(DISTINCTTABLE[Date]), DISTINCTTABLE[Date]< SELECTEDWEEK)
VAR USAGESELECTED = CALCULATE([usage_measure],DISTINCTTABLE[Date] = SELECTEDWEEK)
VAR USAGEPREV = CALCULATE([usage_measure],DISTINCTTABLE[Date] = PREVWEEK)
RETURN USAGESELECTED - USAGEPREV
 
There's only a problem with your view, it is not possibile with matrix in Power Bi, try another way to put it in a matrix.
I attack the pbix file. If it is correct, please accept my reply as solution!
 
BBF
  • sarath_chandra's avatar
    sarath_chandra
    Icon for Helper III rankHelper III
    3 years ago

    Thanks@BeaBF ,you added PN, usage measure ,difference,date in columns ... but i dont need date ( i need usage for selected week ,usage for past week of selected week date as columns) can you please alter the dax ? please