topic Daily used and month total consumed in DAX Commands and Tips

Daily used and month total consumed

ssysanny — Thu, 09 Feb 2023 23:53:24 GMT

could anyone help use DAX to calculate the daily consumed and month consumed quickly after combined sheets named Mmeter and Vmeter together and then calculate them? in sheet sample is only for Mmeter clumsily.

only based on the first 4 columns marked

this is a very simple issue for all of you. thanks.

FGGGasStatistic.xlsx

Re: Daily used and month total consumed

v-yueyunzh-msft — Fri, 10 Feb 2023 02:58:18 GMT

Hi , ssysanny

According to your description, you want to get the "Daily used and month total consumed".

Thanks for your sample data .And for your need in Excel, you need to calculate with the row judgement.

So we need to add an Index column in Power BI Editor both in two tables.

And i add four calculated columns in two tables.There may be some biases in understanding your data, and I think you can download my pbix file to view it and fine-tune your logic.

The end result i get is this, and sorry for i can not unsersatnd the "Summary" worksheet table in your Excel. I think when we has this calculated columns , you can resolve it in Power BI Desktop!

If you still need help with this problem, you can try to describe your requirements calculation process in more detail and give the results you want in the end as a table to me.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: Daily used and month total consumed

ssysanny — Fri, 10 Feb 2023 06:28:13 GMT

v-yueyunzh-msft

many thanks for your work. however my request is as attached and need to calculate it by DAX and also other assistant columns (Dailyused,AccUsed,DailyUsedCalc) are not neccesary.

further need one date table and then these sheets are fact table to build it. thanks.

20230210Daily used and month total consumed.pbix

Re: Daily used and month total consumed

v-yueyunzh-msft — Mon, 13 Feb 2023 03:59:53 GMT

Hi , ssysanny

According to your description, i may got missing in your need.And thanks for the test .pbix you provide with me.

Do you mean you want to make the "Vmeter" and the "Mmeter" show both in one matrix visual?
If this , you can refer to these steps.

(1)We can create a dimension table like this:

(2)We need to update the table relationship between your tables:

(3)Then we can create two measures:

Measure = var _cur_row = MAX('Row Header'[RowNames]) return IF(_cur_row="Vmeter" ,SUM(Vmeter[AccUsed]),SUM('Mmeter'[DailyUsedCalc]))

Measure 2 = SUMX(VALUES('Row Header'[RowNames]) , [Measure])

(4)Then we can put the [Measure 2] on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: Daily used and month total consumed

ssysanny — Wed, 15 Feb 2023 07:33:03 GMT

thanks you very much. v-yueyunzh-msft

actually in these 2 sheets the original columns are as follow. so my question is how to :

1, need create date table to build relationship

2, how to create measures to get the dailyused number(in table named dailyused/dailyusedcalc) and also the accused number(in table named accused)

3, and then to use these measures to create that sample tabel.

Re: Daily used and month total consumed

v-yueyunzh-msft — Thu, 16 Feb 2023 01:36:34 GMT

Hi, ssysanny

Do you mean you want to get the value in measure not in the calculated column in the 'Vmeter' and the 'Mmeter'?

And by onther my end result is right or not ?Dose the Matrix is the end result you are looking for(just update the measure to contains the calculated columns)?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: Daily used and month total consumed

ssysanny — Thu, 16 Feb 2023 08:10:54 GMT

v-yueyunzh-msft

thanks for your time and work.

yes, other assistant columns are not needed and directly calculate by the original columns.

this one is right and expected for monthly number and also need to get the accumulative number. thanks.

Re: Daily used and month total consumed

v-yueyunzh-msft — Fri, 17 Feb 2023 01:55:05 GMT

Hi, ssysanny

Do you mean you want to convert the calcualted columns to the measure?But in your screenshot , i can not understand how to calcuate the value of the 'Vmeter'.

You can try to refer to this dax:

Measure = var _cur_row = MAX('Row Header'[RowNames])var _t= ADDCOLUMNS( ALLSELECTED('Vmeter') , "Dailyused2" , var _next_row = FILTER(ALLSELECTED('Vmeter'),'Vmeter'[Index]=EARLIER(Vmeter[Index])+1) return IF([Index]=1, 0 , [CloseBalance]-MAXX(_next_row,[CloseBalance])))var _t1 = ADDCOLUMNS(_t ,"AccUsed2" , var _value=14054 var _t= FILTER(_t,'Vmeter'[Index]<EARLIER('Vmeter'[Index])) return _value+ SUMX(_t,[Dailyused2]))var _t2 = ADDCOLUMNS( ALLSELECTED('Mmeter') , "Dailyused2" ,var _index=[Index] var _next_row = FILTER(ALLSELECTED('Mmeter'),'Mmeter'[Index] = _index+1) return [CloseBalance]-MAXX(_next_row,[CloseBalance])+MAXX(_next_row,[ReCharges]) )var _t3 =ADDCOLUMNS(_t2,"DailyUsedCalc2", var _last_index = MAXX(_t2,[Index]) return IF([Index]=_last_index , 0 ,[Dailyused2]))returnIF(_cur_row="Vmeter" ,SUMX( FILTER(_t1,MONTH( [DateRead]) =MONTH(MAX('tblDate'[Date])) && year( [DateRead]) =year(MAX('tblDate'[Date]))) , [AccUsed2]),SUMX( FILTER(_t3,MONTH( [DateRead]) =MONTH(MAX('tblDate'[Date])) && year( [DateRead]) =year(MAX('tblDate'[Date]))) , [DailyUsedCalc2]) )

Measure 2 = var _t =ADDCOLUMNS( CROSSJOIN( CROSSJOIN(ALLSELECTED('Row Header'[RowNames]),ALLSELECTED('tblDate'[MonthNumber])) , VALUES('tblDate'[Year])) , "v" , [Measure])var _date = VALUES('tblDate'[MonthNumber])var _row =VALUES('Row Header'[RowNames])var _year =VALUES(tblDate[Year])returnSUMX( FILTER(_t , [MonthNumber] in _date && [RowNames] in _row && [Year] in _year) , [v])

For this , you can delete the calcualted columns in the raw table.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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