Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello world,
new to DAX and Power bi here, I have tried to create a dynamic graph that would give the user the ability to see the difference between current month and previous month, but with a twist.!The user can select different year/month through another graph, so I want the graph to be updated.
So...I have created this calculation for the current month:
This is after the selection, where I want to be updated to show the updated data.
Solved! Go to Solution.
Eventually I followed another path, and this is the solution I found
this is the code for the current month/selected month
Same year Current Month =
VAR selectedyear =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Year] ),
FIRSTNONBLANK (
'Time Table'[Year],
'Time Table'[Year]
),
YEAR ( TODAY () )
)
)
RETURN
VAR selectedmonth =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Month Name] ),
FIRSTNONBLANK (
'Time Table'[Month Number],
'Time Table'[Month Number]
),
MONTH ( TODAY () )
)
)
RETURN
CALCULATE (
[Count],
'Time Table'[Year] = selectedyear,
'Time Table'[Month Number] = selectedmonth,
ALLSELECTED ( 'Time Table'[Month Number] ),
ALLSELECTED ( 'Time Table'[Year] )
)
this is the one for the previous to selected,with a provision for january.
Same Year Prev Month =
VAR selectedyear =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Year] ),
FIRSTNONBLANK (
'Time Table'[Year],
'Time Table'[Year]
),
YEAR ( TODAY () )
)
)
RETURN
VAR selectedmonth =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Month Name] ),
FIRSTNONBLANK (
'Time Table'[Month Number],
'Time Table'[Month Number]
) - 1,
MONTH ( TODAY () ) - 1
)
)
RETURN
VAR selectedmonth2 =
CALCULATE ( IF ( selectedmonth = 0, 12, selectedmonth ) )
RETURN
VAR selectedyear2 =
CALCULATE ( IF ( selectedmonth = 0, selectedyear - 1, selectedyear ) )
RETURN
CALCULATE (
[Count],
'Time Table'[Year] = selectedyear2,
'Time Table'[Month Number] = selectedmonth2,
ALLEXCEPT (
'Time Table',
'Time Table'[Day Number]
)
)
For some reason that I haven't yet figured out this one works unlike the solution of @v-shex-msft. It must be an issue of our data rather than the solution itself.
Nonetheless thank for your help and inspiration!
HI @Anonymous,
You can try to use the following measure formula if they meet for your requirements:
Selected/Current Month Count =
IF (
COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
CALCULATE (
[Count],
ALLSELECTED ( 'Time Table' ),
VALUES ( 'Time Table'[Date] )
),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Month Number] = MONTH ( TODAY () )
&& 'Time Table'[Year] = YEAR ( TODAY () )
)
)
)
Previous month of Selected/Current Month Count =
VAR selected =
MAX ( 'Time Table'[Date] )
VAR prevSelected =
DATE ( YEAR ( selected ), MONTH ( selected ) - 1, DAY ( selected ) )
VAR prevCurrent =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
RETURN
IF (
COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Year] = YEAR ( prevSelected )
&& 'Time Table'[Month Number] = MONTH ( prevSelected )
)
),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Year] = YEAR ( prevCurrent )
&& 'Time Table'[Month Number] = MONTH ( prevCurrent )
)
)
)
BTW, you can also take a look at the following link about use date function to manually define filter range for calculating.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Thanks for your answer.!This seems like something that it could work, although I have no 'Time Table'[Date] field, my only fields available are
Month Number
Year Number
Month ID - which is a field like 202001 to show 2020/01.
Likewise for the days/weeks/quarters.
Well, my only issue is that I have dates that are further than the one today (e.g I have dates in 2022) that have no values for the Count field. Is there any way to obtain the max date that I have data for measure Count?
Also, there's also the issue of January...!e.g if the user selects January of 2018, then the comparison should be made between January 2018 and December 2017, which I think this aforementioned calculation doesn't take into account, as it calculates the Year, maybe with an if in January, it would be more complete.
Unfortunately I can upload no data, sorry.!
Right now I have created this
Metric 1 =
VAR LatestYearRollingNo =
CALCULATE ( IF(ISFILTERED('Time Table'[Month Name]),FIRSTNONBLANK('Time Table'[Year],'Time Table'[Year]),year(TODAY())),
ALLSELECTED ( 'Time Table'[Year] )
)
RETURN
VAR LatestMonthRollingNo =
CALCULATE ( IF(ISFILTERED('Time Table'[Month Name]),FIRSTNONBLANK('Time Table'[Month Number],'Time Table'[Month Number]),month(TODAY())),
ALLSELECTED ( 'Time Table'[Month Number] )
)
RETURN
CALCULATE (
[AR Receipt Count],
'Time Table'[Month Number]= LatestMonthRollingNo,
'Time Table'[Year]=LatestYearRollingNo
)
which works great to show me the selected the month+the last one by default, but I cannot create a similar calculation to show me the previous of the selected month.
Any ideas @v-shex-msft ??
HI @Anonymous,
Can you please share some dummy data with minimum data structure? It will help to test and coding formulas.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
I think this is good enough data to check calcs, please tell me if you wnt more (random number+fake ids, but all the others are ok)
https://drive.google.com/open?id=12uuaGTjAl2I48EjSu3jjX3JPsQ9Z7eB64PgslIUW3Tw
Check the data and let's see what we got.!
Thanks @v-shex-msft !
HI @Anonymous,
You can try to use the following measure formulas if they suitable for your requirement:
Selected/Current Month Count =
IF (
COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
CALCULATE (
[Count],
ALLSELECTED ( 'Time Table' ),
VALUES ( 'Time Table'[Fiscal Month] )
),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Fiscal Month Number] = MONTH ( TODAY () )
&& 'Time Table'[Fiscal Year] = YEAR ( TODAY () )
)
)
)
Previous month of Selected/Current Month Count =
VAR selected =
MAX ( 'Time Table'[Fiscal Month] )
VAR formatted =
DATE ( LEFT ( selected, 4 ), RIGHT ( selected, 2 ), 1 )
VAR prevSelected =
DATE ( YEAR ( formatted ), MONTH ( formatted ) - 1, DAY ( formatted ) )
VAR prevCurrent =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) )
RETURN
IF (
COUNTROWS ( ALLSELECTED ( 'Time Table' ) ) <> COUNTROWS ( ALL ( 'Time Table' ) ),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Fiscal Year] = YEAR ( prevSelected )
&& 'Time Table'[Fiscal Month Number] = MONTH ( prevSelected )
)
),
CALCULATE (
[Count],
FILTER (
ALLSELECTED ( 'Time Table' ),
'Time Table'[Fiscal Year] = YEAR ( prevCurrent )
&& 'Time Table'[Fiscal Month Number] = MONTH ( prevCurrent )
)
)
)
Regards,
Xiaoxin Sheng
Eventually I followed another path, and this is the solution I found
this is the code for the current month/selected month
Same year Current Month =
VAR selectedyear =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Year] ),
FIRSTNONBLANK (
'Time Table'[Year],
'Time Table'[Year]
),
YEAR ( TODAY () )
)
)
RETURN
VAR selectedmonth =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Month Name] ),
FIRSTNONBLANK (
'Time Table'[Month Number],
'Time Table'[Month Number]
),
MONTH ( TODAY () )
)
)
RETURN
CALCULATE (
[Count],
'Time Table'[Year] = selectedyear,
'Time Table'[Month Number] = selectedmonth,
ALLSELECTED ( 'Time Table'[Month Number] ),
ALLSELECTED ( 'Time Table'[Year] )
)
this is the one for the previous to selected,with a provision for january.
Same Year Prev Month =
VAR selectedyear =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Year] ),
FIRSTNONBLANK (
'Time Table'[Year],
'Time Table'[Year]
),
YEAR ( TODAY () )
)
)
RETURN
VAR selectedmonth =
CALCULATE (
IF (
ISFILTERED ( 'Time Table'[Month Name] ),
FIRSTNONBLANK (
'Time Table'[Month Number],
'Time Table'[Month Number]
) - 1,
MONTH ( TODAY () ) - 1
)
)
RETURN
VAR selectedmonth2 =
CALCULATE ( IF ( selectedmonth = 0, 12, selectedmonth ) )
RETURN
VAR selectedyear2 =
CALCULATE ( IF ( selectedmonth = 0, selectedyear - 1, selectedyear ) )
RETURN
CALCULATE (
[Count],
'Time Table'[Year] = selectedyear2,
'Time Table'[Month Number] = selectedmonth2,
ALLEXCEPT (
'Time Table',
'Time Table'[Day Number]
)
)
For some reason that I haven't yet figured out this one works unlike the solution of @v-shex-msft. It must be an issue of our data rather than the solution itself.
Nonetheless thank for your help and inspiration!
A nice implementation i have seen is this
Metrics =
VAR LatestMonthRollingNo =
CALCULATE (
MAX ( 'Time Table','Time Table'[Month] ),
ALLSELECTED ( 'Time Table','Time Table'[Month] )
)
RETURN
VAR LatestYearRollingNo =
CALCULATE (MAX ('Time Table','Time Table'[Year] )),
ALLSELECTED ('Time Table','Time Table'[Year] )
)
RETURN
CALCULATE (
[Counter],
'Time Table','Time Table'[Month]= LatestMonthRollingNo,
'Time Table','Time Table'[Year]=LatestYearRollingNo
)
but unfortunately this doesn't work when I haven't selected any month because (most probably) there are dates for next month, but those dates contain no data on the measure I want to count. So a mix of the previous calc +this one is needed I think but I got no clue how to make the mix.!
User | Count |
---|---|
119 | |
65 | |
65 | |
56 | |
50 |
User | Count |
---|---|
177 | |
84 | |
70 | |
64 | |
54 |