cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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:
 Shops Dates Total Loss YTD a 01.08.2024 50000 b 15.08.2024 45000 c 02.09.2024 32000

Thank You

3 REPLIES 3
Regular Visitor

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

Responsive Resident
Please share sample data. Will check
Responsive Resident

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors