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.
gHello guys. There are a couple of things I'm trying to do here:
First, in my Calendar table, I need a calculated column which shows "Current month", "previous month" "2 months ago", etc.. Ex:
Date | Year | Month | Day | Relative month |
12/31/2018 | 2018 | December | 31 | 2 months ago |
1/1/2019 | 2019 | January | 1 | previous month |
1/2/2019 | 2019 | January | 2 | previous month |
2/15/2019 | 2019 | February | 15 | Current month |
2/16/2019 | 2019 | February | 16 | Current month |
2/17/2019 | 2019 | February | 17 | Current month |
3/11/2019 | 2019 | March | 11 | Next month |
3/12/2019 | 2019 | March | 12 | Next month |
3/13/2019 | 2019 | March | 13 | Next month |
The last column is what I need, a calculated column that will change every new month.
Second, I will use that calculated column to do a filter selector, in which my user will be able to select "Current month", "Previous month", etc. in order to check total sales from a Card visual to current year, and another Card visual which shows last year total sales.
Right now, this is my measure for Total Sales Last year:
First measure:
c_Ventas MTD =
IF (MIN(CALENDARIO[Fecha_Master])<=CALCULATE(MAX(VENTAS_DIARIAS[Fecha comercial]),ALL(VENTAS_DIARIAS)),
CALCULATE(SUM(VENTAS_DIARIAS[Total]),DATESMTD(CALENDARIO[Fecha_Master]))
)
Second measure:
c_Total Venta mes A.Ant =
VAR DataMaxDate =
CALCULATE ( MAX ( VENTAS_DIARIAS[Fecha comercial] ), ALL ( VENTAS_DIARIAS ) )
RETURN
CALCULATE (
[c_Ventas MTD],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( CALENDARIO[Fecha_Master] ),
DATESBETWEEN ( CALENDARIO[Fecha_Master], BLANK (), DataMaxDate )
)
)
)
The thing with this is, when I try to apply a Time filter like "month", because it shows an error with
the SAMEPERIODLASTYEAR function. And as I say, im trying to do a filter selector which allows me to see Total sales
from current year MTD (if I select "current month" that shows only this month; if I select "previous month" it shows previous month), but also Total sales from LAST YEAR MTD (if I select "current month" that shows only this month LAST YEAR; if I select "previous month" it shows previous month LAST YEAR
Is there a way to do that?
Thanks in advance, I know you can help me, this should be an easy one.
Omar.
Solved! Go to Solution.
Hi @omarevp,
For the Relative month column, we can create a caluclated column as below to get that.
Relative month2 = VAR _diff = DATEDIFF ( TODAY (), 'Table1'[Date], MONTH ) RETURN SWITCH ( _diff, -2, "2 months ago", -1, "Previous month", 0, "Current month", 1, "Next Month" )
For your second question, kindly share your pbix to me.
Regards,
Frank
Hi @omarevp,
For the Relative month column, we can create a caluclated column as below to get that.
Relative month2 = VAR _diff = DATEDIFF ( TODAY (), 'Table1'[Date], MONTH ) RETURN SWITCH ( _diff, -2, "2 months ago", -1, "Previous month", 0, "Current month", 1, "Next Month" )
For your second question, kindly share your pbix to me.
Regards,
Frank
Hi @omarevp
Can you plaease post data and expected results on Google Drive or OneDrive and share the link here to formulate a solution.
Cheers
CheenuSing
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |