Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
@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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |