Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

create measure based on column values?

Hi All,

 

I have created Running Total based on calendar date.

14.PNG

 

But due to requirement i would like to have Year,Quarter,Month,Weekday,Weeknum in slicer.

13.PNG

Thus i created calculated table for that.

11.PNG

Running Total was working fine when we give date column from Calendar table but i would like to give "Period" instead of that so that based on slicer selection it should get reflected.

This was the running Total formula which i used.

12.PNG

Now my question is can we create a measure based on the values selected in "Period".

 

For Eg.,

 

If period=Year then "need a measure value which holds running total Year wise"

else if Period = Quarter then "need a measure value which holds running total Quarter wise"

else if Period = Month then "need a measure value which holds running total Month wise"

and so on

 

I tried to create switch statement in column but count is not matching properly and in Measure i coulnd't able to write Case statement since it is showing error.

 

Any Help would be very greatful.

 

Thanks in advance

 

1 ACCEPTED SOLUTION
Adamtall
Resolver III
Resolver III

Hi

Maybe this can help you.

You need a calendar table to make this work.

(The data you provided, create blank query in PowerBI and copy paste)

Spoiler
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAgCEV34eyBIALXtmMQ919DqA01Mc8X4IM7XNBALUAYkMSQAHNgv4GlnfKfzeHOHO2qUA2IlvKh+GaezBx7pdTsP9G/rsKcCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Source System" = _t, #"01/01/2020" = _t, #"02/01/2020" = _t, #"03/01/2020" = _t, #"04/01/2020" = _t, #"05/01/2020" = _t, #"06/01/2020" = _t, #"07/01/2020" = _t, #"08/01/2020" = _t, #"09/01/2020" = _t, #"10/01/2020" = _t, #"11/01/2020" = _t, #"12/01/2020" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Source System"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}, {"Value", type number}})
in
#"Changed Type1"

 

Try this union.

 

Spoiler
TABLE 2 =
UNION(
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Month] & " - " & 'Table'[Date].[Year];
"value"; 'Table'[Date];
"period"; "Month"
);
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Year] & " - " & 'Table'[Date].[Quarter];
"value"; 'Table'[Date];
"period"; "Quarter"
);
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Year];
"value"; 'Table'[Date];
"period"; "Year"
);
SELECTCOLUMNS(
'Table';
"date"; WEEKNUM('Table'[Date]);
"value"; 'Table'[Date];
"period"; "Weeknum"
))

Acc after max value on date

 

Spoiler
Acc =
var Max_date = MAX('TABLE 2'[value])
Return
CALCULATE(SUM('Fact'[Value]);ALL('TABLE 2') ;'TABLE 2'[value] <= Max_date)

 custom1.PNGcustom2.PNGcustom3.PNG

 

You need to solve the sorting problem, but this works 🙂

 

/Adam

View solution in original post

6 REPLIES 6
Adamtall
Resolver III
Resolver III

Hi

Maybe this can help you.

You need a calendar table to make this work.

(The data you provided, create blank query in PowerBI and copy paste)

Spoiler
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAgCEV34eyBIALXtmMQ919DqA01Mc8X4IM7XNBALUAYkMSQAHNgv4GlnfKfzeHOHO2qUA2IlvKh+GaezBx7pdTsP9G/rsKcCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Source System" = _t, #"01/01/2020" = _t, #"02/01/2020" = _t, #"03/01/2020" = _t, #"04/01/2020" = _t, #"05/01/2020" = _t, #"06/01/2020" = _t, #"07/01/2020" = _t, #"08/01/2020" = _t, #"09/01/2020" = _t, #"10/01/2020" = _t, #"11/01/2020" = _t, #"12/01/2020" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Source System"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}, {"Value", type number}})
in
#"Changed Type1"

 

Try this union.

 

Spoiler
TABLE 2 =
UNION(
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Month] & " - " & 'Table'[Date].[Year];
"value"; 'Table'[Date];
"period"; "Month"
);
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Year] & " - " & 'Table'[Date].[Quarter];
"value"; 'Table'[Date];
"period"; "Quarter"
);
SELECTCOLUMNS(
'Table';
"date"; 'Table'[Date].[Year];
"value"; 'Table'[Date];
"period"; "Year"
);
SELECTCOLUMNS(
'Table';
"date"; WEEKNUM('Table'[Date]);
"value"; 'Table'[Date];
"period"; "Weeknum"
))

Acc after max value on date

 

Spoiler
Acc =
var Max_date = MAX('TABLE 2'[value])
Return
CALCULATE(SUM('Fact'[Value]);ALL('TABLE 2') ;'TABLE 2'[value] <= Max_date)

 custom1.PNGcustom2.PNGcustom3.PNG

 

You need to solve the sorting problem, but this works 🙂

 

/Adam

Anonymous
Not applicable

Hi @Adamtall 

 

Can you please share the Pbix file.

 

Thanks

https://files.fm/u/6uxf9e3w

 

/Adam

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Guys,

 

@v-jayw-msft ,@Greg_Deckler ,@v-lionel-msft ,@Adamtall ,@Anonymous 

 

Any help will be greatful onn the below issue.

 

Thanks in Advance

 

Anonymous
Not applicable

Hi,

 

@v-jayw-msft ,

 

Any suggestion would be helpful.

 

Thanks

amitchandak
Super User
Super User

Formula seems correct. If you are use it with month or qtr or year, is it not showing cummlative results.

Or for Qtr you want rest value after QTR

In such case you have to use DatesMTD, datesqtd etc or totalmtd ..

also refer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Refer formula's

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.