Measurement to show data that is not in my DB

[Anonymous]

I have data as follows.

Trans Date	MarketCode	Currency	Balance
Tuesday, April 7, 2020	1	Other Currency	$  29,976.97
Wednesday, April 8, 2020	1	Other Currency	$  29,976.97
Friday, April 10, 2020	1	Other Currency	$  29,960.97
Monday, April 13, 2020	1	Other Currency	$  30,072.27
Tuesday, April 14, 2020	1	Other Currency	$  30,080.67
Thursday, April 16, 2020	1	Other Currency	$  29,580.47
Friday, April 17, 2020	1	Other Currency	$  29,613.27

These are cash balances per MarketCode and Currency (there is more data, this is just an extract example), and are money balances at the end of the day. The thing is that the current report only shows data if there was a transaction on the day, so in this example, there is no data for April 9th, 11th, 12th, and 15th.

So when I create a visual, with a basic SUM measurement, for those days I get 0.

So when the user selects a date range from the date slicer, What I want to do is if there is no transaction for a specific day (No data on DB), the number should read the last balance found, so for example for day 9th, my balance should read  $29,976.97 (which is the 8th balance).

Again, the formula should include looking for a specific MarketCode, Currency, and if no data for the date in the date slicer, then grab that MarektCode and GLAcocunt for the previous day. And so on.

I have tried some CALCULATE with FILTERS, but no luck.

[amitchandak]

@Anonymous , Create a date table join with this table and try a measure like this

CALCULATE(sum('Table'[Balance]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])),Table['Trans Date'])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

@amitchandak  I'm trying to use that formula, but the last expression the "Table[Trans Date]", is not allowing me to select that field, it only appears all my measurements in that table.

[v-deddai1-msft]

Hi @Anonymous ，

First, create a calendar table for your slicer:

Table2 = CALENDAR(MIN('Table'[Trans Date]),MAX('Table'[Trans Date]))

You can create following measure for it:

Measure =

VAR A =

    MAX ( Table2[Date] )

VAR B =

    CALCULATE ( MAX ( 'Table'[Trans Date] ), 'Table'[Trans Date] < A )

RETURN

    IF (

        HASONEVALUE ( Table2[Date] ),

        IF (

            CALCULATE ( VALUES ( 'Table'[Balance] ), 'Table'[Trans Date] = A )

                = BLANK (),

            CALCULATE ( VALUES ( 'Table'[Balance] ), 'Table'[Trans Date] = B ),

            CALCULATE ( VALUES ( 'Table'[Balance] ), 'Table'[Trans Date] = A )

        ),

        BLANK ()

    )

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQ_5N1xjx0lLqKke6PbQR38BQm_l1WRrPtAJmAv3lymAbA?e=BNF2Oj

Best Regards,

Dedmon Dai

[Anonymous]

@v-deddai1-msft 

I have tried the formula (right graph), and still not showing the dates that have no data. (left graphs is the original measurement, a simple SUM)

[v-deddai1-msft]

Hi @Anonymous ,

Is the screenshot below you want?

Please try to modify the options in the screenshot:

Best Regards,

Dedmon Dai

[Anonymous]

@v-deddai1-msft  I made that change on the X-Axis, but still nothing, What Am I doing wrong? is the formula correct?

I have a One-to-many relationship between my Date and Transaction Table, is it correct?

[v-deddai1-msft]

Hi @Anonymous ,

Would you please show me your pbix file by onedrive for business. Please keep the data used in the line chart and delete other data.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[Anonymous]

Thanks for the support @v-deddai1-msft 

You can find the file that I'm using in the following link

https://1drv.ms/u/s!AqBN-aaBie3Fg29UZ77SuY5k1Udt?e=BOOapa

Also, even if we get it to work with the formula, I'm afraid I might have an issue, I found out that by selecting multiple markets (my top slicer) the graph breaks, it only accepts 1 market, and at the end, I will need to add up the balances of all the markets that are selected in the slicer.

i.e.

Market 1 balance for day 9th is $10

Market 2 balance for day 9, no data, then use balance day 8 $20

My total balance for the day 9h should be 30 if both markets are selected.

These above numbers are not real data in my file, just numbers to explain the logic.

[Anonymous]

@v-deddai1-msft  Hi, any luck, I'm still having this issue.