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

Helper II

Calculate Stock certain month

Hi everyone, i have a table with cars code, buy date and sell date, seller, buyer and city, also a calendar table. The calendar table and the car table are linked by buy date

I have two filters in the page (year and month filter separately)

I want to calculate the stock by the month and year selected, by counting the cars that have been bought before the first day of the month/year selected and hasnt been sold OR had been sold after the same starting day of the month. Also i want to display this by seller and by city in a table to calculate the stock rotation.

How can a i calculate this ? Im using this measure but its not working

Stock autos = CALCULATE(
COUNTROWS(AUTOS),
FILTER(
'AUTOS',
'AUTOS'[FECHA COMPRA] <= FIRSTDATE(Calendario[Fecha])
&& (AUTOS[Fecha Reserva] >= STARTOFMONTH(Calendario[Fecha])
|| ISBLANK(AUTOS[Fecha Reserva]))
&& YEAR(AUTOS[FECHA COMPRA]) = YEAR(SELECTEDVALUE(Calendario[Fecha])
&& MONTH(AUTOS[FECHA COMPRA]) = MONTH(SELECTEDVALUE(Calendario[Fecha]))
)
))

Filters in the page (year selected and month selected)

1 ACCEPTED SOLUTION
Super User

Try

Stock autos =
VAR MinDate =
MIN ( 'Calendar'[Date] )
VAR Result =
CALCULATE (
COUNTROWS ( Autos ),
'Calendar'[Date] <= MinDate,
Autos[Sell date] > MinDate
|| ISBLANK ( Autos[Sell date] )
)
RETURN
Result

You don't need to explicitly specify a filter for the buy date, the relationship with the calendar table will do that, and by specifying a filter on the date column of the calendar table that will automatically remove any filters on the calendar table from your slicers, as long as it is marked as a date table.

Super User

Try

Stock autos =
VAR MinDate =
MIN ( 'Calendar'[Date] )
VAR Result =
CALCULATE (
COUNTROWS ( Autos ),
'Calendar'[Date] <= MinDate,
Autos[Sell date] > MinDate
|| ISBLANK ( Autos[Sell date] )
)
RETURN
Result

You don't need to explicitly specify a filter for the buy date, the relationship with the calendar table will do that, and by specifying a filter on the date column of the calendar table that will automatically remove any filters on the calendar table from your slicers, as long as it is marked as a date table.

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