Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dax Expression

I have table with date wise data,i want to show the value based on month end.

If it have value in jan 31st and dosnot have value in feb 28 then i want to show same value in 28 feb.

Please help me to create dax expression

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @v-rzhou-msft ,

Thanks For the response.

This solution meets 90%  and only issue was i dont want to sum up Quantity.

ie expected result shown below 

 

Month  Count

Jan        1000

Feb       1000

Mar       1000

Apr        500

May       500

Jun        500

 

The above one is based on the file you shared

View solution in original post

Hi @Anonymous 

Which code did you use in this screenshot? I think you didn't build a relatisonship in above sample. I think you want yo show the QTY at the last day instead sum of the QTY of the whole month. Could you tell me why Jan, Feb and Mar show 2000 in your screenshot? Please show me the result you want by screenshot. 

 

Best Regards,
Rico Zhou

 

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

 

View solution in original post

10 REPLIES 10
collinq
Super User
Super User

Hi @Anonymous ,

 

I think that this request might be better in the developer forum so I have moved it there.  Also, I think a bit more explanation is going to be needed as to what you are truly trying to do - what is the actual thing that you are trying to do, from a logic perspective?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Anonymous
Not applicable

Hi @collinq ,

Thanks for the response.

Am trying to figure out sum of customer count in each month.

Let say in 31 jan 2021 active customer count is  2000,then feb there is no customer addition and customer count in 28 feb is 2000.So here there is no change in count ,i will not get the data from source,because we pulling data that have a change.In this case there is no record for particular operator in Feb but i want show count in visual that is same as january.Again the if the march month also dont have any change,i want show same figure as in january.

 

 

Hi @Anonymous 

I think you want to create a measure to show rolling total by month.

Here I build a sample.

1.png

Build a date table by dax.

Date =
VAR _T =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 05, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] )
    )
VAR _T1 =
    ADDCOLUMNS (
        _T,
        "Last Date each Month",
            MAXX (
                FILTER ( _T, [Year] = EARLIER ( [Year] ) && [Month] = EARLIER ( [Month] ) ),
                [Date]
            )
    )
RETURN
    _T1

Measure:

Rolling Total = SUMX(FILTER(ALL('Sample'),'Sample'[Date]<=MAX('Date'[Last Date each Month])),'Sample'[QTY])

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

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

Anonymous
Not applicable

Hi @v-rzhou-msft ,

Thanks For the response.

This solution meets 90%  and only issue was i dont want to sum up Quantity.

ie expected result shown below 

 

Month  Count

Jan        1000

Feb       1000

Mar       1000

Apr        500

May       500

Jun        500

 

The above one is based on the file you shared

Hi @Anonymous 

Try this code.

Latest QTY = 
VAR _LatestNotBlankDate =
    MAXX (
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Date] <= MAX ( 'Date'[Last Date each Month] )
        ),
        'Sample'[Date]
    )
VAR _LatestQTY =
    CALCULATE (
        SUM ( 'Sample'[QTY] ),
        FILTER ( ALL ( 'Sample' ), 'Sample'[Date] = _LatestNotBlankDate )
    )
RETURN
    _LatestQTY

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

Anonymous
Not applicable

Hi @v-rzhou-msft ,

Thanks for the response.

I had altered the messure because i want to make some filters in visuals,so i remove ALL function,please correct me if am  wrong.

Also i can see there is no relationship between sample table and date table,it will be help ful if you can explain how its working and when am adding relation the data goes wrong.

Latest QTY =
VAR _LatestNotBlankDate =
MAXX (
FILTER (
'Sample' ,
'Sample'[Date] <= MAX ( 'Date'[Last Date each Month] )
),
'Sample'[Date]
)
VAR _LatestQTY =
CALCULATE (
SUM ( 'Sample'[QTY] ),
FILTER ( 'Sample' , 'Sample'[Date] = _LatestNotBlankDate )
)
RETURN
_LatestQTY
 
Nazeez_0-1632804528711.png

The above one is the result whwn am creating relationships

Hi @Anonymous 

My code will show correct result whether you build relationship between Sample and Date table.

Latest QTY = 
VAR _LatestNotBlankDate =
    MAXX (
        FILTER (
            ALL ( 'Sample' ),
            'Sample'[Date] <= MAX ( 'Date'[Last Date each Month] )
        ),
        'Sample'[Date]
    )
VAR _LatestQTY =
    CALCULATE (
        SUM ( 'Sample'[QTY] ),
        FILTER ( ALL ( 'Sample' ), 'Sample'[Date] = _LatestNotBlankDate )
    )
RETURN
    _LatestQTY

Compare mine with yours, we can see that I use All function in Filter. All function will remove all filters in Sample, calculate the result based on whole sample table. If you don't use All function result will calculate based on current row. So result should be incorrect.

For reference: Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

 

Best Regards,
Rico Zhou

 

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

Anonymous
Not applicable

Hi @v-rzhou-msft ,

Agreed with you,adding relation  will give same result.

I have one more concern ,i want see visual based on some filters in sample table(Date filter working and others not).

Currently its not working,currently i have filtered  based on QTY.Like this in my table i have customer names,please help to fix this

Nazeez_1-1632814512073.png

 

Hi @Anonymous 

Which code did you use in this screenshot? I think you didn't build a relatisonship in above sample. I think you want yo show the QTY at the last day instead sum of the QTY of the whole month. Could you tell me why Jan, Feb and Mar show 2000 in your screenshot? Please show me the result you want by screenshot. 

 

Best Regards,
Rico Zhou

 

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

 

Anonymous
Not applicable

Hi @v-rzhou-msft ,

 

Its by my mistake,i added a duplicate row as same as original data for testing .That is the reason its showing doubled amount.

As you said i want the data as QTY at last day of each month.From your code am getting that result but the issue was i want to filter it by some colums usng slicers.

I had attache screen shot of my actual data and the code used.

 

1.This is the report am developing,here i want to filter values by column in Data table.

But its not considering my filters

Nazeez_2-1632910629526.png

2.Meassure i had used given below

Latest QTY =
VAR _LatestNotBlankDate =
MAXX (
FILTER (
ALL ( 'Data' ),
'Data'[SIM Summary Snapshot: Created Date] <= MAX ( 'Date'[Last Date each Month] )
),
Data[SIM Summary Snapshot: Created Date]
)
VAR _LatestQTY =
CALCULATE (
SUM ( Data[SIM Count] ),
FILTER (ALL( Data), Data[SIM Summary Snapshot: Created Date] = _LatestNotBlankDate )
)
RETURN
_LatestQTY
 
3.Data in the table
Nazeez_3-1632910689516.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.