Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
New to PBI and need some help with a function which can be easily done in excel but which I cannot figure out in PBI.
The following data is used:
Max of TimeSinceNew | Monthyear |
19509 | 201705 |
19595 | 201706 |
19688 | 201707 |
19793 | 201708 |
19907 | 201709 |
20030 | 201710 |
20166 | 201711 |
20210 | 201712 |
Column 1 shows MAX values as only the end of month value is of interest when determining the time the object was used during a month.
I want to add a column showing the difference in these max values per month (here done in excel):
Max of TimeSinceNew | Monthyear | Time used per month |
19509 | 201705 | 0 |
19595 | 201706 | 86 |
19688 | 201707 | 93 |
19793 | 201708 | 105 |
19907 | 201709 | 114 |
20030 | 201710 | 123 |
20166 | 201711 | 136 |
20210 | 201712 | 44 |
Any suggestions on how this can be done?
Hi @BatsBeek
Try this Calculated Column if monthyear is formatted as dates
Time = VAR LastMonth = PREVIOUSMONTH ( TableName[Month_Year] ) RETURN IF ( NOT ( ISBLANK ( LastMonth ) ), TableName[Max of TimeSinceNew] - CALCULATE ( VALUES ( TableName[Max of TimeSinceNew] ), FILTER ( ALL ( TableName ), TableName[Month_Year] = LastMonth ) ) )
I tried this, it says 'the function FILTER is not supported in this context in DirectQuery mode.' I'm sorry I never mentioned using this type of datasource before. Perhaps this only works when using a downloaded database?
Furthermore I managed to use the group function on the original dates to group (bins) the data per month, as I think this way it does read the column as dates. This however still does not work with any of the suggested formulas you gave before.
Thanks again for your help so far.
If MonthYear is not formatted as date then may be this one
Time = VAR LastMonth = MAXX ( FILTER ( TableName, TableName[MonthYear] < EARLIER ( TableName[Monthyear] ) ), TableName[Monthyear] ) RETURN IF ( NOT ( ISBLANK ( LastMonth ) ), TableName[Max of TimeSinceNew] - CALCULATE ( VALUES ( TableName[Max of TimeSinceNew] ), FILTER ( ALL ( TableName ), TableName[MonthYear] = LastMonth ) ) )
Hi @Zubair_Muhammad,
Thanks for your reply, I think we are close to a solution. Monthyear is not formatted as a date as it is derived from other columns in the main dataset which link a month and year to an observation. It is created as year*100+month. When using your second solution the error shown is:
'A single value for column Monthyear in table TableName cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'
Any way to work around this? Thanks in advance for your help.
Hi @BatsBeek
Are there duplicate MonthYear in your dataset?
If Yes, Could you paste an extended dataset please?
I hope I am not going to overcomplicate it but yes there are.
Shown table would be for 1 object, which would report its TimeSinceNew multiple times during one month, so for each of these observations a MonthYear is created.
The complete database contains 1000s of objects, however I am limiting my table to 1 object for now to test out some reporting features (for which the time used during a month is the most crucial part).
Extended dataset for you to experiment on (here I turn off the 'Max of' filter for monthyear).:
Monthyear TimeSinceNew
201707 | 18828 |
201707 | 18829 |
201707 | 18830 |
201707 | 18831 |
201707 | 18832 |
201707 | 18833 |
201708 | 18833 |
201708 | 18834 |
201708 | 18836 |
201708 | 18837 |
201708 | 18840 |
201708 | 18841 |
201708 | 18843 |
201708 | 18844 |
201708 | 18845 |
201708 | 18847 |
201708 | 18898 |
201708 | 18899 |
201709 | 18900 |
201709 | 18901 |
201709 | 18902 |
201709 | 18903 |
201709 | 18904 |
201709 | 18905 |
201709 | 18906 |
201709 | 18907 |
201709 | 18908 |
201709 | 18911 |
201709 | 18945 |
201709 | 18947 |
201709 | 18948 |
201710 | 18949 |
201710 | 18950 |
201710 | 18951 |
201710 | 18952 |
201710 | 18953 |
201710 | 18954 |
201710 | 18955 |
201710 | 18957 |
201710 | 19009 |
201710 | 19011 |
201710 | 19013 |
201711 | 19014 |
201711 | 19018 |
201711 | 19019 |
201711 | 19020 |
201711 | 19023 |
201711 | 19024 |
201711 | 19036 |
201711 | 19038 |
201711 | 19039 |
201711 | 19069 |
201711 | 19070 |
201711 | 19071 |
201711 | 19073 |
201711 | 19074 |
201712 | 19075 |
201712 | 19076 |
201712 | 19078 |
201712 | 19080 |
201712 | 19100 |
201712 | 19102 |
201712 | 19105 |
201712 | 19106 |
201712 | 19107 |
201712 | 19108 |
201712 | 19109 |
201712 | 19110 |
201712 | 19111 |
HI @BatsBeek
In that case, try this MEASURE (Not Calculated Column)
Time = VAR LastMonth = MAXX ( FILTER ( ALL ( TableName ), TableName[MonthYear] < SELECTEDVALUE ( TableName[Monthyear] ) ), CALCULATE ( MAX ( TableName[Monthyear] ) ) ) RETURN IF ( NOT ( ISBLANK ( LastMonth ) ), MAX ( TableName[TimeSinceNew] ) - CALCULATE ( MAX ( TableName[TimeSinceNew] ), FILTER ( ALL ( TableName ), TableName[MonthYear] = LastMonth ) ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |