Skip to main content
cancel
Showing results for 
Search instead 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

Reply
feralvarez994
Helper II
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)
feralvarez994_0-1680708694704.png

 

1 ACCEPTED SOLUTION
johnt75
Super User
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.

View solution in original post

1 REPLY 1
johnt75
Super User
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.

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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