Calculate sales value based on specific date

[Preetsingh86]

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:

Shops	Dates	Total Loss YTD
a	01.08.2024	50000
b	15.08.2024	45000
c	02.09.2024	32000

Thank You 

[Preetsingh86]

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

[Rupak_bi]

Please share sample data. Will check

[Rupak_bi]

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.