Видео

Hi, can you explain a little more what you are looking for ?

Thanks

I fixed it with a extra measure with a SWITCH statement to check from Gross Margin and there calculate the %. But it feels kinda hard coded, as i don't reference to the PnL Measure. (so making a change would needed at both places

@joriskempen5650 What I did (in my course for daryl sheridan) is I put an extra line in my layout (excel) for gross margin. Then I add in the PNL measure in the switch an extra line refering to a seperate measure. Why seperate measure you think. Otherwise the difference measure break on the different datatypes (number and percentage). In the same line I format this line as percentage. In PNL measue add: _SelecetedHeaderPL = "GM % Revenue", FORMAT([GM % Revenue],"0.00%"), Example : GM % Revenue = VAR _GrossMargin = CALCULATE([Amount Actuals], // Warning ! do not use the variable but the actual measure REMOVEFILTERS(ProfitAndLoss_Layout ), // First we remove all filters from the P&L Layout table, PBI knows the row/filter context ProfitAndLoss_Layout[PnL Section] = "Revenue" // We determine the Filtercontext and whoala !! We use the || for the OR statement || ProfitAndLoss_Layout[PnL Section] = "Cost of Sales" ) VAR _Revenue = CALCULATE([Amount Actuals], // Warning ! do not use the variable but the actual measure REMOVEFILTERS(ProfitAndLoss_Layout ), // First we remove all filters from the P&L Layout table, PBI knows the row/filter context ProfitAndLoss_Layout[PnL Section] = "Revenue" ) VAR _GrossMarginRevenue = DIVIDE(_GrossMargin,_Revenue) Return _GrossMarginRevenue Alternative in a video of SQLbits is to use calculation groups in tabular editor 3 around minute 20. Didnt tried this but if i will and it works i add a small addon video ruclips.net/video/mTa34gtJhYA/видео.htmlsi=DG5b4HNTuiO_68Iz

@@rauljimenez5485 thank you. Those words from you means a lot to me.

@@MutomboGumbo your welcome. Hope you will find better videos to suit your needs

@@quentinvandevondel3230 thanks, just post a new video with compacter and more flexibel pnl dax measure. I think you will like it

I am glad to hear that. My pleasure . You are welcome

Yes exactly :-)

Thanks mate, yes it gives confidence if those kind of people are positive about the course

Well thank you, will look into it. Within a couple of weeks my P&Lin an hour course will be released.

So if I understand correctly you have a calculated column with a dax-formula that contains date in period. Then using the outcome of that column in the trend-measure doesn't work ?

Thank you, my pleasure. Hope it will help you on your Power BI journey.

Hi, Sorry for the late reaction. It is probally the HeaderBS[IndexPartFS] Column. It is formatted as text column and not as a Integer. In the VAR part je can also change the numbers between quotes "1" into number 1 and leave the tables as it is. Power BI says you now compare text with a number coliumn.

Hope this will answer your question. let me know.

My pleasure. @MA MU have a nice Power BI journey!

Hi Vishal Thanks, and good questions I start with Q2 The use of creating the Finance Pivot Table is for own development and analyze in the building phase. To ensure I could see which accounts (Accountkeys) had actual bookings (lowest level) on them. So I used it to be sure I did the flattening the right way, because the original accountstable is a parent-child hierarchy we flattened (by the way see my other video’s for not manual way to flatten in M and Dax) Also in this demo-file from microsoft the accounts table contains AccountKeys for other performance indicators than Accounts like 96 Headcount and 99 Square Footage. I wanted to be sure I could remove/filter them out Q1 The SubHeadersDetails is just a formule see file ‘03 PL Adventure Works to FS Data’ =IF([@AccLevel4]<>[@AccLevel5],1,0) . Checking if accountslevel 4 and 5 are the same or not. Handy to know with expanding later on in visual, so you could suppress the lowest level if they are the same The subheadersort Manually. The Sign and report Sign, In this Case manually, (some Intermediate/Expert addition below) The Sign and report Sign only because of microsoft made everything already positive (in datawarehouse). Normally if you connect (or extract) from a bookkeeping system it is not necessary (revenue negative, costs positive). You could just make one DAX measure and the result is already adding up. Alternative…. skip report column and only use sign column. In real life you could also skip sign column and do it complete in DAX measure. The next measure is the example of skipping report sign. Actuals Frank = VAR _SelectedHeader = SELECTEDVALUE(Header[Header]) VAR _ActualAddsUpxmin1 = Sumx( values(Accounts[Sign]), [Actual]*Accounts[Sign]*-1) VAR _GrossMargin = CALCULATE([Actual Adds Up], Header[Header] = "Revenue" || Header[Header] = "Cost of Sales", REMOVEFILTERS(Header) ) VAR _OperatingProfit = CALCULATE([Actual Adds Up], REMOVEFILTERS(Header), Header[Header] = "Revenue" || Header[Header] = "Cost of Sales" || Header[Header] = "Operating Expenses" ) VAR _NetIncome = CALCULATE([Actual Adds Up], REMOVEFILTERS(Header), Header[Header] = "Revenue" || Header[Header] = "Cost of Sales" || Header[Header] = "Operating Expenses" || Header[Header] = "Other Income and Expense" || Header[Header] = "Taxes" ) VAR _Result = SWITCH(TRUE(), _SelectedHeader = "Revenue", [Actual Adds Up], _SelectedHeader = "Cost of Sales", _ActualAddsUpxmin1, _SelectedHeader = "Gross Margin", _GrossMargin, _SelectedHeader = "Operating Expenses",_ActualAddsUpxmin1, _SelectedHeader = "Operating Profit", _OperatingProfit, _SelectedHeader = "Other Income and Expense", [Actual Adds Up], _SelectedHeader = "Taxes", _ActualAddsUpxmin1, _SelectedHeader = "Net Income", _NetIncome, [Actual Adds Up] ) return _Result

@@controloverreports WOOH! I didn't expect to get the answer, Thanks a lot for the detailed explanation. It's really helpful.

Thanks mate

Hi Syed, Let's see your approach. By using sameriod or dateadd on Previousdate or referenedate you are trying to change the period. If you succeed you will get a longer period (barchar with 26 months after eachother). We want to change the measure and show for the same month a measure with actual this year and actual last year right ?. So we start with making the measure [Actuals Adds Up LY] : = Actuals Adds up LY = CALCULATE([Actual Adds Up LY], SAMEPERIODLASTYEAR('Calendar'[Date])) Then we copy the measure [ Actuals L13M] and rename to [ Actuals L13M LY ] and replace only 'Actuals Adds up' with 'Actuals Adds up LY': Actuals L13M LY = VAR ReferenceDate = Max ('Calendar'[Date]) VAR PreviousDates = DATESINPERIOD( 'Calendar Trend'[Date.Trend ], ReferenceDate, -13, MONTH ) VAR Result = CALCULATE( [Actual Adds Up LY], // only change for Syed REMOVEFILTERS('Calendar'), KEEPFILTERS(PreviousDates), USERELATIONSHIP('Calendar'[Date],'Calendar Trend'[Date.Trend ]) ) Return Result For you i also upload this file with these two extra measures to the financialbackpack (link below video) Let me know if it works and if you like it :-)

Hi Luis. Thank you. I agree wih you. For your own department/team that need full permissions (to build reports based on the dataset), a the normal workspace is good to use the dataset and the excel file. For other audience then your own team I use the App (like management). The app is one place with several reports. So they have access to the reports in a userfriendly way but not to the dataset(s). Also the excel file has kind of the same look as power bi in the app.

Good morning Frank. Thanks for your input. I will take your advice.

Thank you for your kind words! My pleasure

HI Zakaria Dadili. In this sample-dataset (adventurework) there is no DimHeader-table (or DimRatio) that i am aware of (see Zip-file CSV's). So you need to add it. You can type it directly in Power BI (Enter Data on the Home tab of Power BI desktop) or use a table (in this example i used an excel table). If you (try) create the table DimHeader in DAX using group by (in Dax) on COA you will miss the subtotals (Gross profit margin etc) rows. Also you will miss the calc type column but there is a workaround for. The subheaderesdetail isn't necessary but nice to have to supress blank lower level collapse (if is AccLevel5 = Acclevel4 then 0 else 1 in power query, i think you can do this in adding a conditional column) . The sign and report sign column can be replaced by multiple 1 *-1 directly in a measure (instead f a column) by spliting the COA in pieces by using VAR XX and then switch statement on (sub)header level. In adventurework little more difficult than reallife because of every number in the amount-column of the general ledger is made possitive. Normally you could perform the VAR on header level. The subheaderSort is normally based on accounting-rules. Sometimes you can retreive this sort/category column already out of a system. In this case Adventurwork there is no such table so i add it manually to the COA by starting at 100 for P&L on acc level 4 (think you can try to do automatic in power query if you like). Hope this helps.

@@controloverreports I don't understan 100% of that comment; but the 85% sounds amazing!! thks

Thank you my pleasure

Hi David, well not that i am familiar with, that use case you describe (the oter way around) to make the ParentChild hierarchy instead of unflatten it, i didn't ran into in my workarrea. Did you need this for power bi /excel ?

@@controloverreports for power bi. For a QuickBooks file.. Just for practicing though for now.

Thank you Paolo, hope you like the serie.

Thank you. Hope it give you some extras to your power bi journey.

This dataset is from Adventure works from Microsoft. You can download the datasource excelfile in the downloadlink below. In a couple of weeks I will post a video from searching for this datasource to building the datamodel to making a dashboard.

Hi Diaz, no you dont have to create a new slicer. You just can use the excisting slicer. In your new measure and visual you can use the calendar table that will interact with the slicer.

Hi Sajine, in the calendar you can have fiscal columns, the relationship is on date, you make an slicer with a fiscal period. This method will return the period based on date minus 13 months, so you would be fine.