Based on selected month, show if it had "Revenue" last 18 months

[PeterStuhr]

Hi all,

I have a sales table with "Company", "Revenue", "Order Date".

I would like to make a table containg "Company" and a "Measure".

I need a "Yes / No" measure that shows whether a company has had revenue for the last 18 months based on selected month.

Example:

If I select "Jan2020", I want a table showing the companies that had revenue for Jan2020 and 18 months back only.

[jdbuchanan71]

@PeterStuhr 

Give something like this a try.

18 months sales = 
VAR _EndDate = LASTDATE(DATES[Date])
VAR _MonthsWithSales = 
    CALCULATE(
        COUNTROWS(
            FILTER(
                DISTINCT(DATES[Month Year]),NOT ISBLANK([Sales Amount])
            )
        ),DATESINPERIOD(DATES[Date],_EndDate,-18,MONTH)
    )
RETURN
IF ( _MonthsWithSales = 18, "Yes", "No")

When I read your question I thought you meant you wanted to know if they had sales in EVERY month for the last 18 months.  If you mean in any month in the last 18 then the solution from @amitchandak  will be your answer.

[amitchandak]

@PeterStuhr , Try like

Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-18,MONTH))+0
Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-18,MONTH))  +0

Active Company = if([Rolling 18]>0, "Yes","No")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[PeterStuhr]

Hi, when I slice for a month then it filters my companies to only show if they had in the filtered month.

Is it because I have a relationship with Date and Sales Date?

[v-yiruan-msft · Accepted solution]

Hi @PeterStuhr ,

Yes, it should be. It will display the data which the date is selected when the relationship be created between Dates and Sales base on the date fields. Please delete the relationship and create a measure as below:

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _selpre18 =
    DATE ( YEAR ( _seldate ), MONTH ( _seldate ) - 18, DAY ( _seldate ) )
VAR _rev =
    CALCULATE (
        SUM ( 'Sales'[Revenue] ),
        FILTER (
            'Sales',
            'Sales'[Order Date] <= _seldate
                && 'Sales'[Order Date] >= _selpre18
        )
    )
RETURN
    IF ( _rev > 0, "Yes", Blank() )

Best Regards

Rena