Creating Table Based on Actual Month and Before Dates

[erhan_79]

Hi there ;

Could ypu pls help me to create a new table about below issue ;

I have table  A as below which includes "Material", "Delivery Date " and " Quantity" :

 We will createa a new table , and in thşs table we will sum of the qty of the materials based on a date filtering , for new table  rules will be like that :

• If the delivery date is in actual month and before actual month , system will sum that  quantities .For example today 15.01.2021 , actual month is January then , even there will be a date after 15.01.2021 but in January 2021 , it will be calculated , but if there will be a date after january 2021 (as above picture yellow marked = February 2021 ) system will not add to calculate this line .
• If the delivery date will be blank , system will not calculate that quantity too.

So after these rules , i want a calculated table as below :

• For A material all quantities are  calculated because dates are in actual month
• For B material one of the line's date is after actual month so system calculated only quantity = 40 's line 
• For C material all quantities are  calculated because dates are in actual month
• For D material one of the line's date is blank   so system calculated only quantity = 50 's line 

Also i share with you excel source with below link : 

https://drive.google.com/file/d/1apdTMsR2j2tnd0rzPLCEcb-iIoLSxsYm/view?usp=sharing

Thanks in advance for your kind supports 

[v-henryk-mstf · Accepted solution]

Hi @erhan_79 ,

According to your request, I have done the following test for reference. The calculated column is created for dynamic calculation. If the current month is February, the sum value corresponding to Material is "100".

C = 
CALCULATE (
    SUM ( 'Table'[Quantity] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Material] = EARLIER ( 'Table'[Material] )
            && 'Table'[Delivery Date] <> BLANK ()
            && (
                'Table'[Delivery Date] <= TODAY ()
                    || (
                        'Table'[Delivery Date] > TODAY ()
                            && MONTH ( 'Table'[Delivery Date] ) = MONTH ( TODAY () )
                    )
            )
    )
)

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[parry2k]

@erhan_79 I don't see any reason to create a table or column when the above result can be easily achieved using the measure (as per my solution). There has to be a strong reason why someone needs a calculated column or calculated table in the model, and visualization is not a very strong reason. Just my 2 cents.

[VijayP]

@erhan_79 

Answer is given in this file. Let me know if that suffice.

[erhan_79]

dear @VijayP 

thanks for your reply but i wonder something , in your actual quantity formula you mentioned  " today" as i marked yellow. for example today was 01.02.2021 , and then actual month will be february , if there will be in the  delivery date  05.02.2021 tomorrow's report system should calculate too.But when you write the formula Today() , i could not be sure that you considered only date of today or all whoe month of january, also i can not test this situation because today is the last day of january .

maybe more clear like that to tell , imagine that today 30.01.2021 , and you sent me this formula and i changed the one of the delivery date to 31.01.2021 , so system will consider that changed date to 31.01.2021 's Lines's too ?

[VijayP]

@erhan_79 

Instead of Today you can use Month(Delivery DAte) > Month(Today()) to avoid such issues.

[erhan_79]

ok @VijayP  , but when we do like that then previous years are not calculated , for previous years we need to add something too , 

[erhan_79]

@VijayP , 

i try to tell that actual month is dynamic , when i open report in february actual month will be february , so all the delivery dates in february from 1 to 29 february will be the actual month statu whenever i check the report in february 

[parry2k]

@erhan_79 solution attached, if a date is selected in the slicer, it will sum qty on or before that date, if no date is selected then it will use TODAY() date.

You can tweak this solution as you see fit.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[erhan_79]

hi @parry2k  

thank you very much for your suport but i need a calculated column , yours is measure 🙂

[parry2k]

@erhan_79 why you want it as a column? What is the rationale behind it?

[erhan_79]

@parry2k i want a new table ,as below , i have another formula which one is already created  for tables , and will work with below table