Snapshot data

[Anonymous]

Hello Community - Below is a modified sample of data extracted from our ERP system.    The accounts (40000, 40010, etc) are general ledger accounts.   In order to get a Month to Date value, in this case for total Februrary, I need to filter on a Create Date of March 2nd, and the As Of Date of March 1st.    The system always reports reconciled numbers one day behind.  

I can drag these into a table and get the total Feb result...but what I really want is a measure.    And I'd like to be able to apply this to all months...for example...to be able to put all months in a column chart showing the totals for each month.  

Problem is I don't know how to create a formula to do that?   Any help is appreciated.  

[Greg_Deckler]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Thanks Greg -

I had hoped posting the table would be enough.   But to elaborate:  

I have a date table with the "as of" date connected to the "dates" in the date table.  

I specifically need to connect to these particular accounts, but with the added challenge that our ERP system only reconciles the prior month on the 1st day of the next month.    So a final January number only becomes available the first day of February. 

Literally what I am trying to do is to achieve a measure that replicates the table below and shows the total sum  (which is cut off in the picture).    To do so, I somehow need to get at these specific accounts, and utlize the "create date" and the "as of" dates, and the "actual value".  

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may apply virtual relationship in DAX measure.

• https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/
• https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the As of Date column of your Data Table to the Date column of your Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year and Month.  To your visual, drag Year and Month from the Calendar Table.  Write this measure

=SUM(Data[Actual Value])

Hope this helps.

[Anonymous]

@Ashish_Mathur  I have this exact setup in my date table already.   

I like your approach as it is very simple, but it's not working because I think I need to grab those particular accounts from the Description field.    Your approach is causing Jan Feb and March to show gigantic numbers becuase I beleive it is summing all of the "actual value" data.     I only need the actual values from those specified general accounts from our accounting system. 

[Ashish_Mathur]

Hi,

Create a slicer of Account and select the ones youw want.