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 

[v-denglli-msft]

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.

[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.