Forum Discussion

Preetsingh86's avatar
Preetsingh86
Regular Visitor
2 years ago

Calculate sales value based on specific date

Hi Guys

 

I have been asked to calculate losses before and after date column. Starting the calculation from Financial year start which is 01.09.2023 until the date column and then seperate measure for calculations from date column until present.

 

I tried following measure but not giving me correct date

  • Loss After date =
var totalLossAfter = CALCULATE([Total Loss], FILTER(ALL('Calendar'), 'Calendar'[Week Ending] > LASTDATE('Macro Stores'[Completion Date])))
var weeksAfterToday = CALCULATE(DISTINCTCOUNT('Calendar'[Week Ending]), FILTER(ALL('Calendar'), 'Calendar'[Week Ending] > TODAY()))
var weeksAfter = CALCULATE(DISTINCTCOUNT('Calendar'[Week Ending]), FILTER(ALL('Calendar'), 'Calendar'[Week Ending] > LASTDATE('Macro Stores'[Completion Date]))) - weeksAfterToday

RETURN
DIVIDE(totalLossAfter, weeksAfter)
 
  • Loss Before date= var totalLossBefore = CALCULATE([Total Loss], FILTER(ALL('Calendar'), 'Calendar'[Week Ending] < FIRSTDATE('Macro Stores'[Completion Date])))
var weeksBeforeRemove = CALCULATE(DISTINCTCOUNT('Calendar'[Week Ending]), FILTER(ALL('Calendar'), 'Calendar'[Week Ending] < DATE(2021,08,29)))
var weeksBefore = CALCULATE(DISTINCTCOUNT('Calendar'[Week Ending]), FILTER(ALL('Calendar'), 'Calendar'[Week Ending] < FIRSTDATE('Macro Stores'[Completion Date]))) - weeksBeforeRemove

RETURN
DIVIDE(totalLossBefore, weeksBefore)
 
Here is the scenario mentioned below:
ShopsDatesTotal Loss YTD 
a01.08.202450000 
b15.08.202445000 

c

02.09.202432000 

Thank You 

4 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    2 years ago

    hi Preetsingh86 

    You may try this
    loss Before= 
    Var min_date = calculate(min(table,date),all(table))

    Var to_date = max(table,date)
    return
    calculate(sum(table,loss),all(table),table,date>=min_date&&table,date<=to_date)
    Same way you can make the after loss as well.
    If this doesnot work, please share sample data.


  • Preetsingh86's avatar
    Preetsingh86
    Regular Visitor
    2 years ago

    Hi Rupak thank you for replying, but I am afraid this didn't work 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Preetsingh86 ,

    Can you provide some sample data? We can better understand the problem and help you.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.