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.
How do I return the [_Inc Count] for the equivalent number of previous months based on the months the user selects (whether that is 1 or more)?
Details:
I have a previous month formula as follows:
Solved! Go to Solution.
This is the solution I ended up going with (measures)...
To get the date range:
This is the solution I ended up going with (measures)...
To get the date range:
Based on the details I produced an example on DAX.DO. You can experiment with it at -> https://dax.do/NwmaUnx44zszNB/ .
I assumed you might be displaying a single value.
MEASURE 'Sales'[PreviousPeriodSales] =
VAR _MinDate =
MIN ( 'Date'[Date] )
VAR _MaxDate =
MAX ( 'Date'[Date] )
VAR _NumMonths =
( DATEDIFF ( _MinDate, _MaxDate, MONTH ) + 1 ) * -1
VAR _PrevEndDate =
DATEADD (
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Date] = _MinDate
),
-1,
DAY
)
VAR _PrevStartDate =
STARTOFMONTH (
DATEADD (
CALCULATETABLE (
VALUES ( 'Date'[Date] ),
REMOVEFILTERS ( 'Date' ),
'Date'[Date] = _MinDate
),
_NumMonths,
MONTH
)
)
VAR _PrevDateRange =
DATESBETWEEN ( 'Date'[Date], _PrevStartDate, _PrevEndDate )
RETURN
CALCULATE ( [Sales Amount], _PrevDateRange )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
@Anonymous , Try like
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |