topic Re: Calculate sales value based on specific date in DAX Commands and Tips

Calculate sales value based on specific date

Preetsingh86 — Wed, 04 Sep 2024 12:22:38 GMT

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

Re: Calculate sales value based on specific date

Rupak_bi — Wed, 04 Sep 2024 14:02:19 GMT

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.

Re: Calculate sales value based on specific date

Preetsingh86 — Thu, 05 Sep 2024 12:11:11 GMT

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

Re: Calculate sales value based on specific date

Rupak_bi — Thu, 05 Sep 2024 12:48:38 GMT

Please share sample data. Will check

Re: Calculate sales value based on specific date

Anonymous — Thu, 26 Sep 2024 11:39:06 GMT

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.